Need help on using COUNT and SUM together in MySQL

Question

I have got 2 tables: Institute & Course

Institute
---------
i_id       i_name      i_city
------     --------    --------
1          Name 1      London
2          Name 2      Manchester
3          Name 3      London

Course
-------
c_id       i_id        stream
------     -------     --------
1          1           Engineering
2          1           Engineering
3          2           Engineering
4          3           Engineering

Now I am trying to achieve 3 things:

a) Number of Institutes per city offering Engineering courses. b) Total (distinct) Number of Institutes offering Engineering courses.

I started writing below query to achieve this:

SELECT institute.i_city, 
COUNT( DISTINCT institute.i_id ) AS institute_count_per_city
FROM institute, course
WHERE institute.i_id = course.i_id
AND course.stream =  'Engineering'
GROUP BY institute.i_city

I got below result:

i_city      institute_count_per_city
-------     -------------------------
London      2
Manchester  1

Now I have achieved the count of institutes per city.

I can't figure how can I get the total number of institutes in same query which in the above example will be 3 (2+1)

I would really appreciate the help.

MarcinJuraszek · Accepted Answer · 2013-04-20 19:46:45Z

3

Use ROLLUP:

SELECT institute.i_city, 
COUNT( DISTINCT institute.i_id ) AS institute_count_per_city
FROM institute, course
WHERE institute.i_id = course.i_id
AND course.stream =  'Engineering'
GROUP BY institute.i_city WITH ROLLUP

It will add additional row with SUM of your aggregated values.

Update

GrandTotal version:

SELECT IFNULL(institute.i_city, 'GrandTotal'), 
COUNT( DISTINCT institute.i_id ) AS institute_count_per_city
FROM institute, course
WHERE institute.i_id = course.i_id
AND course.stream =  'Engineering'
GROUP BY institute.i_city WITH ROLLUP

edited Apr 20, 2013 at 19:46

answered Apr 20, 2013 at 18:50

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andomar Over a year ago

+1 This would give you an additional row with city null and the total count of institutes that offer an engineering ocurse.

Rishab Garg Over a year ago

Thanks a lot. This is perfect! Can I replace null with something else like GrandTotal?

Rishab Garg Over a year ago

Thanks mate. That's what I wanted. It works like charm now!! Really appreciate your help.

MarcinJuraszek Over a year ago

@RishabGarg You're welcome! And because you're new here: you should read How does accepting an answer work?. That's how StackOverflow works :)

Dan Bracuk · Accepted Answer · 2013-04-20 18:52:09Z

1

Would a union query help?

 your existing query
 union
 select 'total' I_city, count(*) institute_count_per_city
 FROM institute, course
 WHERE institute.i_id = course.i_id
 AND course.stream =  'Engineering'
 GROUP BY 'total'

answered Apr 20, 2013 at 18:52

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Collectives™ on Stack Overflow

Need help on using COUNT and SUM together in MySQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related