CASE statement with SUM 1

[ethorn10]

Hello all...

Quite some time ago, I got a query straightened out here by some helpful MVPs. My situation has changed slightly. Previously, I had a query that was doing a SUM for each month. Now I'd like to add to that given a condition but I'm stuck in one mode of thinking. It isn't adding up right and I can tell why, I just can't fix it. What I have so far:

SELECT 
CASE WHEN a.extra_id = 1 THEN (format(sum(b.price+25),0))
ELSE (format(sum(b.price),0)) END AS total,
date_format(a.date_applied, '%M' ) AS date_applied 
FROM tbl_details a inner join
	tbl_sessions b on a.id = b.id
WHERE a.paid=1 
	AND b.sessiontitle = "demo" 
GROUP BY date_format(a.date_applied, '%m' )
ORDER BY MONTH(a.date_applied)

I realize why I'm getting incorrect totals (the CASE statement) but, like I said, I can't get outside of this box I'm in. Help is greatly appreciated.

 

[ethorn10]

Forgot to mention, MySQL 4.1.15.

 

[TonyGroves]

It looks like the SUM should be moved outside the CASE:
[tt]
SELECT
FORMAT(
SUM(
CASE
WHEN a.extra_id = 1 THEN (b.price+25)
ELSE (b.price)
END
),
0
)
AS total,
date_format(a.date_applied, '%M' ) AS date_applied
FROM
tbl_details a
inner join tbl_sessions b on a.id = b.id
WHERE a.paid=1 AND b.sessiontitle = "demo"
GROUP BY date_format(a.date_applied, '%m' )
ORDER BY MONTH(a.date_applied)
[/tt]

 

[ethorn10]

Augh!! Nice work Tony! I had a feeling it wasn't much, just got stuck in that wrong mindset. A star for you.

Thanks again.