SUM by group and SUM of all

[kurky17]

Hi,

I'm actually drafting a MySQL query to be fed into Jasper Reports, and since I can no longer manipulate the resulting data in PHP as it will automatically be translated into a chart by jasper, I need my mysql statement to be able to generate what I need on its own.

Let me start with the table structure of table `package_price`:
(this is not my actual table, just for sample purposes)

fields: id, package_id, price

*package_id is not unique

Now, the situation is, on my website I have checkboxes (e.g. Package1, Package2, Package3, ALL) which, depending on what packages were chosen, should display the total price by package. The ALL option does not mean checking all packages...it is an option on its own..which means that, if Package1 and ALL are chosen, it should display the total price of all rows where package_id = Package1 ...and... the total price of all rows.

Example:

Package1 - 2345
All - 78904

Keep in mind that checkboxes on PHP are stored in an array...so, if I choose Package1 and ALL, given that my checkbox variable name is package...my array should be: package[0] = Package1, package[1] = ALL.

My question is, how do I translate it to a mysql statement? If, without the ALL option, my mysql/php statement should be: (pls don't mind the syntax)

foreach($package as $p){
SELECT SUM(price) from package_price where package_ID = $p GROUP BY package_ID;
}

With the ALL option, how do I incorporate everything into one mysql query statement?

I hope I explained my situation clear enough.

Thanks,
michelle

[urtrivedi]

I am not able to understand your requirement, though you have tried your best to explain.
So I am giving one mysql syntax (found in mysql manual) that will add the sum at the end with null description.

SELECT year, SUM(profit) profit FROM sales GROUP BY year WITH ROLLUP;

year, profit
-------------
2001,60000
2002,50000
null,110000

I hope this is what you are looking for.

[kurky17]

Hi Urtri,

Oh that is just the kind of thing I was looking for. Thanks a lot. I have a follow-up question though, what if I need to add a WHERE clause? I've read up on this mysql website but I didn't find anything.

Thanks,Michelle

[urtrivedi]

SELECT YEAR, SUM(profit) profit FROM sales 
where year>2000
GROUP BY YEAR WITH ROLLUP

[kurky17]

So what will be the result of this query statement? Will it sum up all rows or just those with year>2000?

Because I'm looking for something that will sum up all the rows despite the condition in the where clause. Like in your example, it should include in the summation those entries with year not greater than 2000.

[urtrivedi]

query with where will sumup only those rows which are displayed, it will not sum up rows filtered by where condition. you have to take care in your php code.

[kurky17]

I see. Thanks so much Urtrivedi. You've been a great help. Now I guess I'll have to read up on Jasper Reports because that's where I'll use it.