0

I have a database, which hold data of customers. I need to know how many customers We have in individual city and country. I have to do it with single query.

My table is customers and I have columns city and country(both are varchar), which holds informations about it.

Desired output of query should look like this: 

City | NumberOfCustomers | Country | NumberOfCustomers |
--------------------------------------------------------

Thanks

nifelvind
  • 25
  • 6
  • The desired output is weird. You're welcome – zerkms Mar 06 '14 at 20:52
  • "I have to do it with single query" --- what technical reasons are behind this requirement? – zerkms Mar 06 '14 at 20:53
  • @zerkms "Because my teacher told me so." – WWW Mar 06 '14 at 21:01
  • @Crontab: I bet it's not that, but something like "because 1 query is faster than 2" :-) – zerkms Mar 06 '14 at 21:04
  • @zerkms You are probably right. =) – WWW Mar 06 '14 at 21:05
  • @Crontab Because program will be build on it and requirement is to make it with single query. I am no pro in SQL, still learning. – nifelvind Mar 06 '14 at 21:15
  • @zerkms Can you tell me why is my desired output weird ? I just need to know how many customers are in individual city and in country. Sorry if its unclear. Didnt know how to make it more clear. – nifelvind Mar 06 '14 at 21:16
  • "Because 1 query is faster" --- it's not. For this task 1 query will be much more complicated and slower. – zerkms Mar 06 '14 at 21:18
  • "Can you tell me why is my desired output weird ?" --- because you're mixing different entities in the same result set. "Sorry if its unclear." --- it is clear. And it is weird. You need to perform 2 queries: Number of customers per city; Number of customers per country – zerkms Mar 06 '14 at 21:19
  • @zerkms I see. Is it even possible ? I made it with UNION but its quite messy as it creates only one column and put city and country data into it. I guess I used it in bad way. – nifelvind Mar 06 '14 at 21:23
  • @nifelvind: it is possible but it doesn't worth it. Just use 2 queries – zerkms Mar 06 '14 at 21:24

1 Answers1

0
select city, country, count(*) from tbl group by 1,2 with rollup

Edit to show example table and output of that query:

mysql> select * from location;
+-------+---------+
| city  | country |
+-------+---------+
| City1 | US      |
| City2 | US      |
| City2 | US      |
| City3 | CA      |
| City3 | CA      |
| City3 | JP      |
+-------+---------+
6 rows in set (0.00 sec)

mysql> select city, country, count(*) from location group by 1,2 with rollup;
+-------+---------+----------+
| city  | country | count(*) |
+-------+---------+----------+
| City1 | US      |        1 |
| City1 | NULL    |        1 |
| City2 | US      |        2 |
| City2 | NULL    |        2 |
| City3 | CA      |        2 |
| City3 | JP      |        1 |
| City3 | NULL    |        3 |
| NULL  | NULL    |        6 |
+-------+---------+----------+
8 rows in set (0.01 sec)
atxdba
  • 5,158
  • 5
  • 24
  • 30