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.


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.


Re: SUM by group and SUM of all 80 80

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

I hope this is what you are looking for.

Re: SUM by group and SUM of all 80 80

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.


Re: SUM by group and SUM of all 80 80
SELECT YEAR, SUM(profit) profit FROM sales 
where year>2000
Re: SUM by group and SUM of all 80 80

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.

Re: SUM by group and SUM of all 80 80

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.

Re: SUM by group and SUM of all 80 80

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.