How to use count and group by at the same select statement

Question

I have an sql select query that has a group by   I want to count all the records after the group by statement   Is there a way for this directly from sql  For example  having a table with users I want to select the different towns and the total number of users  select town  count    from user group by town   I want to have a column with all the towns and another with the number of users in all rows   An example of the result for having 3 towns and 58 users in total is    Town         Count Copenhagen   58 NewYork      58 Athens       58

User · Answer

if You Want to use Select All Query With Count Option  try this      select a     Select count b name  from table name as b where Condition  as totCount from table name  as a where where Condition

User · Answer

I know this is an old post  in SQL Server   select  isnull town  TOTAL   Town  count    cnt from    user group by town WITH ROLLUP  Town         cnt Copenhagen   58 NewYork      58 Athens       58 TOTAL        174

User · Answer

Ten non-deleted answers  most do not do what the user asked for   Most Answers mis-read the question as thinking that there are 58 users in each town instead of 58 in total   Even the few that are correct are not optimal   mysql gt  flush status  Query OK  0 rows affected  0 00 sec   SELECT  province  total cities     FROM         SELECT  DISTINCT province  FROM  canada   AS provinces     CROSS JOIN   SELECT  COUNT    total cities  FROM  canada   AS tot   --------------------------- --------------    province                    total cities    --------------------------- --------------    Alberta                             5484     British Columbia                    5484     Manitoba                            5484     New Brunswick                       5484     Newfoundland and Labrador           5484     Northwest Territories               5484     Nova Scotia                         5484     Nunavut                             5484     Ontario                             5484     Prince Edward Island                5484     Quebec                              5484     Saskatchewan                        5484     Yukon                               5484    --------------------------- --------------  13 rows in set  0 01 sec    SHOW session status LIKE  Handler      ---------------------------- -------    Variable name                Value    ---------------------------- -------    Handler commit               1         Handler delete               0         Handler discover             0         Handler external lock        4         Handler mrr init             0         Handler prepare              0         Handler read first           3         Handler read key             16        Handler read last            1         Handler read next            5484     -- One table scan to get COUNT      Handler read prev            0         Handler read rnd             0         Handler read rnd next        15        Handler rollback             0         Handler savepoint            0         Handler savepoint rollback   0         Handler update               0         Handler write                14       -- leapfrog through index to find provinces    ---------------------------- -------    In the OP s context   SELECT  town  total users     FROM         SELECT  DISTINCT town  FROM  canada   AS towns     CROSS JOIN   SELECT  COUNT    total users  FROM  canada   AS tot    Since there is only one row from tot  the CROSS JOIN is not as voluminous as it might otherwise be   The usual pattern is COUNT    instead of COUNT town    The latter implies checking town for being not null  which is unnecessary in this context

User · Answer

You can use DISTINCT inside the COUNT like what milkovsky said  in my case   select COUNT distinct user id  from answers votes where answer id in  694 695     This will pull the count of answer votes considered the same user id as one count

User · Answer

With Oracle you could use analytic functions   select town  count town   sum count town   over    total count from user group by town   Your other options is to use a subquery   select town  count town    select count town  from user  as total count from user group by town

User · Answer

Try the following code   select ccode  count empno   from company details  group by ccode

User · Answer

The other way is      Number of rows in a derived table called d1     select count    from        Number of times each town appears in user       select town  count      from user   group by town   d1

User · Answer

This will do what you want  list of towns  with the number of users in each    select town  count town   from user group by town   You can use most aggregate functions when using GROUP BY   Update  following change to question and comments   You can declare a variable for the number of users and set it to the number of users then select with that   DECLARE  numOfUsers INT SET  numOfUsers   SELECT COUNT    FROM user  SELECT DISTINCT town   numOfUsers FROM user

User · Answer

You can use COUNT DISTINCT          SELECT COUNT DISTINCT town   FROM user

User · Answer

If you want to select town and total user count  you can use this query below   SELECT Town   SELECT Count    FROM User   Count  FROM user GROUP BY Town

User · Answer

If you want to order by count  sound simple but i can t found an answer on stack of how to do that  you can do           SELECT town  count town  as total FROM user         GROUP BY town ORDER BY total DESC

[sql] How to use count and group by at the same select statement

Examples related to sql

Examples related to count

Examples related to group-by