如何从数据透视表中获得总计数
问题描述:
我正在试图获得一个数据透视表,显示该行最后的总数。这是可能的..但是,我无法得到它的工作。我有数据的数据,但我无法得到每行的总数。有没有另外一种方法来完成这个?任何援助非常感谢。如何从数据透视表中获得总计数
select sch.name, s.grade, s.schid, count(s.schid),
(select count(ss.schid) from students ss
where ss.enroll_status=0
and ss.schid = s.schid
group by ss.schid) as Total
from students s
inner join schools sch on
s.schid = sch.schloc
where s.enroll_status=0
group by sch.name,s.grade, s.schid
order by sch.name,s.schid, s.grade;
这是它如何出现什么
|School |Grade |Loc |Count |Total |
|Amery Middle |7 |2740 |233 | 813 |
|Amery Middle |8 |2740 |218 | 813 |
|Porter Elem |3 |12830 |2 | 68 |
|Porter Elem |4 |12830 |2 | 68 |
这是怎么需要出现在数据透视表:在学校中列有傲人的成绩排显示计数,然后总计跨越为学校总招生
| |count|count|count|count|count |count|Grand Total|
|School |3rd |4th |5th |6th |7th |8th | |
|Amery Middle |2 |2 |0 |0 |0 |0 |868 |
|Porter Elem |0 |0 |0 |0 |233 |218 |813 |
感谢您提供的任何帮助。
答
你可以这样说:
SELECT NAME,
"Count 3rd",
"Count 4th",
"Count 5th",
"Count 6th",
"Count 7th",
"Count 8th",
"Count 3rd" +
"Count 4th" +
"Count 5th" +
"Count 6th" +
"Count 7th" +
"Count 8th" "Grand Total"
FROM (SELECT sch.name,
s.grade,
s.schid
FROM students s
INNER JOIN schools sch ON s.schid = sch.schloc
WHERE s.enroll_status = 0)
PIVOT (COUNT(*) FOR (grade) IN (3 AS "Count 3rd",
4 AS "Count 4th",
5 AS "Count 5th",
6 AS "Count 6th",
7 AS "Count 7th",
8 AS "Count 8th"));
这假定总计列是学生的所有成绩之和(即所有的学生都在一个档次),所有牌号有已被选中。
非常感谢你......这就像一个魅力! – teelee
谢谢你的助手......你知道有没有办法选择截然不同的东西?当我在两个查询中选择“DISTINCT”时,都会删除所有数据并为每个成绩列显示“1”。我收到了一些重复项目,这些重复项目将计数关闭。感谢您给予的任何帮助! – teelee
你可能会问这是一个新的问题,完整的数据和预期的输出(就像你对这个问题所做的那样 - 谢谢你,顺便说一下)。 – Boneist