4

In Oracle SQL, how do I return count(*) column along side of a regular column?

What works:

select count(*) from TABLE where username = 'USERNAME';

What I'd like to work:

select username,count(*) from TABLE where username = 'USERNAME';

So I want the username along side of the count, to expand this into another query that lists numerous usernames and the count of their records.

Error:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:
Error at Line: 7 Column: 7

Question:

So, how do I do it?

Xonatron
  • 15,622
  • 30
  • 70
  • 84

1 Answers1

12
SELECT username,count(*) from TABLE WHERE username='USERNAME' GROUP BY username

should do the trick!

The reason the first query works is because MySQL can automatically convert that query into an aggregate query, because it "understands" that you want to count all of the rows where username='USERNAME'. The second query isn't clear enough - you're trying to perform an aggregate function on rows selected by the query, but you also want the rows of the query. My query makes it clear that you only expect one username returned out of the set, so aggregation is not a problem.

Sean Johnson
  • 5,567
  • 2
  • 17
  • 22
  • 5
    I'd leave the filter in the `WHERE`. I guess the optimiser will probably push it down anyway but just in case. – Martin Smith Jun 26 '12 at 15:24
  • 2
    @MartinSmith Agreed, `WHERE` still makes more sense - I prefer `HAVING` only for aggregates; makes the purpose clearer. – Dan Jun 26 '12 at 15:25
  • 1
    Thank you @MartinSmith, I've updated my query. Matthew, This is an improvement over the last one because it only aggregates for the username you're selecting, rather than aggregating ALL usernames and then selecting just the one you want at the end of the aggregation. – Sean Johnson Jun 26 '12 at 15:28
  • 1
    @MatthewDoucette the result will be the same, but putting the condition in `WHERE` makes it more clear that it's the individual rows that have the username you're interested in, and not the group as a whole. Also, with `HAVING` a naive database (though probably not a real one) would still scan the entire table grouping by username, and only then select the group you care about. – Dan Jun 26 '12 at 15:28
  • 1
    @MatthewDoucette - You want to filter by the user name then do the grouping only on matching rows rather than group all rows then keep only matching groups. Whether or not any RDBMS would give a different plan for the two I'm not sure. – Martin Smith Jun 26 '12 at 15:29