March 3, 2015 at 12:03 pm
The following query works very well with the exception that I need to only extract data where the [Grand Total] is >4 and I can't figure out how to make that work.
Select [DT_TERM] AS [Term],[Division Long Name],[Division],[Department],[SECTION],[COURSE], Ethnicity, [A], , [C], [D], [F], [W], [IB], [IC], [ID], [IF],[P], [NP],[A]++[C]+[D]+[F]+[W]+[IB]+[IC]+[ID]+[IF]+[P]+[NP] AS [GradeTotal]
From
(
Select a.[COURSE], a.[CCCCO_ID],GRADE,[Division Long Name],[Division],[Department], [Gender Type],b.Ethnicity, [DT_TERM],[SECTION]
From [SX] a
INNER JOIN ST sp
ON a.CCCCO_ID = sp.CCCCO_ID and a.TERM = sp.TERM
INNER JOIN Ethnic_Group b
ON sp.ETHNIC_GRP = b.Ethnic_Group
INNER JOIN GENDER_Look_Up d
ON sp.GENDER = d.GENDER
INNER JOIN Courses_by_Division c
ON a.COURSE = c.Course
INNER JOIN [TERM_Look Up] e
ON a.TERM = e.TERM
Where a.TERM like '147'
) p
PIVOT
(Count ([CCCCO_ID])
For [GRADE]
IN ([A], , [C], [D], [F], [W],[IB],[IC],[ID], [IF],[P], [NP])
) AS pvt;
March 3, 2015 at 12:24 pm
Add this to the bottom:
WHERE [A]++[C]+[D]+[F]+[W]+[IB]+[IC]+[ID]+[IF]+[P]+[NP] > 4
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 3, 2015 at 12:53 pm
I put that right below pvt; and it says Incorrect syntax near the keyword 'WHERE'
March 3, 2015 at 12:57 pm
You need to remove the semi-colon
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 3, 2015 at 1:00 pm
Thanks so much. I am just learning the crosstab function and sometimes can't see the trees for the forrest.
March 3, 2015 at 1:20 pm
Start here:
http://www.sqlservercentral.com/articles/T-SQL/63681/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply