March 13, 2014 at 7:17 pm
Hello, I've been working on this query where I am trying to produce a single value the sum of all the case statements. Unfortunately on occasion it produces two records. Im thinking union all? Any ideas appreciated
SELECT A + B + C AS total
FROM ( SELECT DISTINCT
MAX( CASE WHEN CRS.CN IN ( 'G21021', 'G21022', 'G21031',
'G21032', 'G21000', 'G21011',
'G21012' )
AND TST.PT = 1
AND TST.RS < 3 THEN 1
ELSE 0
END ) AS A,
MAX ( CASE WHEN CRS.CN IN ( 'G22021', 'G22022', 'G22031',
'G22032', 'G22000', 'G22001',
'G22002' )
AND TST.PT = 2
AND TST.RS < 3 THEN 2
ELSE 0
END ) AS B,
MAX ( CASE WHEN TST.PT IN ( 1, 2 )
AND TST.RS >= 3
AND CRS.CN NOT IN ( 'G21021', 'G21022',
'G21031', 'G21032',
'G21000', 'G21011',
'G21012', 'G22021',
'G22022', 'G22031',
'G22032', 'G22000',
'G22001', 'G22002' )
THEN 4
ELSE 0
END ) AS C
FROM MST
INNER JOIN STU
INNER JOIN SEC ON STU.SC = SEC.SC
AND STU.SN = SEC.SN ON MST.SC = SEC.SC
AND MST.SE = SEC.SE
INNER JOIN CRS ON MST.CN = CRS.CN
INNER JOIN TST ON STU.ID = TST.PID
WHERE ( SEC.DEL = 0 )
AND ( TST.ID IN ( 'DST' ) )
AND ( TST.TA = 313 )
AND ( STU.ID = 4066398 )
AND ( STU.SC = 20 )
GROUP BY CRS.CN
) AS d
March 14, 2014 at 3:09 am
I wonder why only two records.
GROUP BY CRS.CN implies a separate row for every CN value in the join result.
May be you mean SUM (A+B+C) in the outer select list ?
March 14, 2014 at 4:38 am
When you get two records as a result this implies the inner query (starting with SELECT DISTINCT) is returning two records.
Select and execute the inner query to see if the result is matching your expectations. If not: most likely your JOIN or your WHERE statement is not specified correctly...
March 14, 2014 at 4:46 am
Will the results be as expected when you move the MAX and GROUP BY from the inner query to the outer query? This will always result in one single row.
SELECT MAX(A + B + C) AS total
FROM ( SELECT DISTINCT
CASE WHEN CRS.CN IN ( 'G21021', 'G21022', 'G21031',
'G21032', 'G21000', 'G21011',
'G21012' )
AND TST.PT = 1
AND TST.RS < 3 THEN 1
ELSE 0
END AS A,
CASE WHEN CRS.CN IN ( 'G22021', 'G22022', 'G22031',
'G22032', 'G22000', 'G22001',
'G22002' )
AND TST.PT = 2
AND TST.RS < 3 THEN 2
ELSE 0
END AS B,
CASE WHEN TST.PT IN ( 1, 2 )
AND TST.RS >= 3
AND CRS.CN NOT IN ( 'G21021', 'G21022',
'G21031', 'G21032',
'G21000', 'G21011',
'G21012', 'G22021',
'G22022', 'G22031',
'G22032', 'G22000',
'G22001', 'G22002' )
THEN 4
ELSE 0
END AS C
FROM MST
INNER JOIN STU
INNER JOIN SEC ON STU.SC = SEC.SC
AND STU.SN = SEC.SN ON MST.SC = SEC.SC
AND MST.SE = SEC.SE
INNER JOIN CRS ON MST.CN = CRS.CN
INNER JOIN TST ON STU.ID = TST.PID
WHERE ( SEC.DEL = 0 )
AND ( TST.ID IN ( 'DST' ) )
AND ( TST.TA = 313 )
AND ( STU.ID = 4066398 )
AND ( STU.SC = 20 )
) AS d
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply