Retrieve sum() from more than one field

[moondran]

Hi Guys/Girls

 

I have a table that looks like this:

 

id  plant_id  week  year    reading

1  2            20      2012  10

2  3            20      2012  12

3  2            19      2012  10

 

I'm using thi to get the total between weeks, it works perfect:

$sql_week1 = "SELECT 
plant_id, SUM(reading) 
FROM `table_diesel_site`
WHERE ((week BETWEEN '$first_week' AND '$last_week') AND year='$year')
GROUP BY plant_id
";

 

My question is, how can I get the total for week 19 and total of week 20 and the total of week 19+ week 20 in one query to echo them like: Total week 19 = Total week 20= Grand Total =

 

The string above gives me the grand total but I need the total of week 19 and 20 aswell. Can it be done in one query or will I have to create 3 seperate querys for each week?

 

Thanks

 

 

[Jessica]

Why don't you just do the math in PHP instead of another query?

[The Little Guy]

You could try using rollup, which would look something like this:

SELECT 
plant_id, SUM(reading) 
FROM `table_diesel_site`
WHERE ((week BETWEEN '$first_week' AND '$last_week') AND year='$year')
GROUP BY plant_id, week with rollup

 

you may need to reverse plant_id and week in the group by

[Barand]

assuming only 2 weeks will be  selected

SELECT 
plant_id, 
SUM(IF(week=$first_week, reading, 0)) as week1, 
SUM(IF(week=$last_week, reading, 0)) as week2, 
SUM(reading) as total
FROM `table_diesel_site`
WHERE ((week BETWEEN '$first_week' AND '$last_week') AND year='$year')
GROUP BY plant_id

[moondran]

Thanks Barand, works perfect!!