How can I use Count(*) twice in one sql statement?
-
I have a database with a list of items and users for each item. Different users can both have the same item. If I want to get a count of the items for a single person I can do this: $query = "SELECT beerBrewery, COUNT(*) as brewery FROM uniqueBeers WHERE userID = $userID GROUP BY beerBrewery ORDER BY brewery desc"; That will give me the number of breweries for a certain user. I am having trouble trying to get results if I want a count for multiple users. Lets say I have two users and I want to get the number of breweries for each user. How would I go about constructing an SQl statement for that scenario?
-
Answer:
You can group by more than one column: $query = "SELECT beerBrewery, userID, COUNT(*) as brewery FROM uniqueBeers WHERE userID IN ($userID1, $userID2) GROUP BY beerBrewery, userID ORDER BY brewery desc";
Bill Karwin at Quora Visit the source
Other answers
Are you looking for getting grouped counts or individual kinds counts not associated with a group by. has it right if you want grouped counts. If you want to be able to get counts not associated with a group by you can use analytic functions to get N number of individual counts. I changed the SQL to use two of the 6 users in my dual subquery. The first analytic function gets the count of breweries associated with the two users. The second analytic functions gets the total count of brewaries for all selected users, in this case the two in the where clause. SELECT distinct userID,count(*) over( partition by userID) beerBrewery_cnt_by_usr, count(*) over( partition by 1 ) total_beerBrewery_cnt --in filter clause FROM ( select 'user1' userID, 'bb11' beerBrewery from dual union all select 'user2' userID, 'bb11' beerBrewery from dual union all select 'user3' userID, 'bb11' beerBrewery from dual union all select 'user4' userID, 'bb12' beerBrewery from dual union all select 'user5' userID, 'bb12' beerBrewery from dual union all select 'user1' userID, 'bb13' beerBrewery from dual union all select 'user2' userID, 'bb13' beerBrewery from dual union all select 'user3' userID, 'bb13' beerBrewery from dual union all select 'user4' userID, 'bb14' beerBrewery from dual union all select 'user5' userID, 'bb15' beerBrewery from dual union all select 'user5' userID, 'bb16' beerBrewery from dual union all select 'user6' userID, 'bb17' beerBrewery from dual )uniqueBeers WHERE userID in ('user1','user5') outputs: USERID BEERBREWERY_CNT_BY_USR TOTAL_BEERBREWERY_CNT ------ ---------------------- --------------------- user1 2 5 user5 3 5
Andrew Hansen
Related Q & A:
- How can I use SSL with django?Best solution by Stack Overflow
- How can I use XMP in Sharepoint metadata?Best solution by Stack Overflow
- How can I use mongo-sync?Best solution by github.com
- How can I transfer my music from one iPod to another?Best solution by Yahoo! Answers
- How can I transfer my bookmarks from one yahoo account to another?Best solution by Yahoo! Answers
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.