需要帮助的在MySQL
问题描述:
我有2个表使用COUNT和SUM在一起:研究所&课程需要帮助的在MySQL
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
现在我想实现三件事情:学院每个城市发行
a)数字工程课程。 b)总计(不同)提供工程课程的机构数量。
我开始写下面的查询来实现这一目标:
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_city institute_count_per_city
------- -------------------------
London 2
Manchester 1
现在我已经实现了每个城市的机构的数量。
我想不通我怎么能得到相同的查询机构的总数,其在上面的例子中为3(2 + 1)
我会很感激的帮助。
答
使用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
这将增加额外的行与聚合值中SUM
。
更新
GrandTotal
版本:
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
答
请问联合查询帮助?
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'
+0
非常感谢。 – 2013-04-20 21:23:50
+1这将给你一个额外的行'城市'null和提供工程ocurse的机构的总数。 – Andomar 2013-04-20 18:52:07
非常感谢。太棒了!我可以用其他类似GrandTotal替换null吗? – 2013-04-20 19:45:22
我已经更新了我的答案。 – MarcinJuraszek 2013-04-20 19:46:53