It just doesn't look right !!!!

[Maeltar]

hey and hello, been lurking for a while and thought would pluck up the courage to sign up and post a problem I have with an sql query...

 

The SQL works fine, but I need to double check the results.....

 

The query ...

 

select SUM(tmp.prod_price) as 'Total Discount Price', SUM(products_prdV2.price_prd) as 'Total Full Price', ROUND(SUM(products_prdV2.price_prd - tmp.prod_price),2) as 'Total Discount Given', round((SUM(tmp.prod_price) / SUM(products_prdV2.price_prd)) * 100 ,2 ) as 'Gross Percentage Discount' from prd_discounts, tmp, products_prdV2 where prd_discounts.discount_prd_id = tmp.tmp_prodid and tmp.tmp_prodid = products_prdV2.id_prd and prd_discounts.discount_sum > 0;

 

 

gives output of :-

 

+----------------------+------------------+----------------------+---------------------------+

| Total Discount Price | Total Full Price | Total Discount Given | Gross Percentage Discount |

+----------------------+------------------+----------------------+---------------------------+

|              1181.47 |          1450.76 |              269.29 |                    81.44 |

+----------------------+------------------+----------------------+---------------------------+

 

Am just thinking the Gross Percentage Discount looks wrong... and am hoping one of you can say, yup it right or nope, go back and start again....

 

round((SUM(tmp.prod_price) / SUM(products_prdV2.price_prd)) * 100 ,2 ) is what am using to get the percentage, is the formula right ???

 

Once I have the basic SQL right I can then throw it into some php 

 

[ignace]

Your table structure and field naming make it very unclear what contains what. The basic formula for calculating percentages is:

 

(small / big) * 100 (eg (1024 / 1100) * 100 ~ 93%) just remember 80/100 so you know where which value should come.

[riwan]

Nope you got it wrong.

You shouldn't use Total Discount Price/Total Full Price *100 to get the percentage.

You should use Total Discount Given/Total Full Price *100

[Maeltar]

Thanks guys...  should have googled first really :/

 

round(((SUM(products_prdV2.price_prd) - SUM(tmp.prod_price)) / SUM(products_prdV2.price_prd)) * 100 ,2 )

does the trick

 

 

The reason the sql is complicated is thats a second query is it's against a previous query that creates a temp table...

 

this is what the whole thing looks like...

 

create table tmp select orderdetail_ode.pid_ode as 'tmp_prodid', orderdetail_ode.pprice_ode as 'prod_price' from orderdetail_ode, order_ord where order_ord.id_ord = orderdetail_ode.idord_ode and order_ord.status_ord like "Complete%" AND week(order_ord.date_ord) = week(CURDATE()-2);

select SUM(tmp.prod_price) as 'Total Discount Price', SUM(products_prdV2.price_prd) as 'Total Full Price', ROUND(SUM(products_prdV2.price_prd - tmp.prod_price),2) as 'Total Discount Given', round(((SUM(products_prdV2.price_prd) - SUM(tmp.prod_price)) / SUM(products_prdV2.price_prd)) * 100 ,2 ) as 'Gross Percentage Discount' from prd_discounts, tmp, products_prdV2 where prd_discounts.discount_prd_id = tmp.tmp_prodid and tmp.tmp_prodid = products_prdV2.id_prd and prd_discounts.discount_sum > 0;

 

Thanks again for your help..

 

[Maeltar]

just changed the code slightly so that the table tmp is now a temporary table...

 

create temporary table tmp select orderdetail_ode.pid_ode as 'tmp_prodid', orderdetail_ode.pprice_ode as 'prod_price' from orderdetail_ode, order_ord where order_ord.id_ord = orderdetail_ode.idord_ode and order_ord.status_ord like "Complete%" AND week(order_ord.date_ord) = week(CURDATE()-2);