Обсуждение: Query Assistance
Is anyone able to tell me why in the last column of the returned result set, the value calculated is always 0?
QUERY:
SELECT products.productid,
products.cost,
products.srp,
CASE WHEN products.srp > 0 THEN (products.srp - products.cost) * 100 / products.srp ELSE 0 END AS
margin,
products.type,
products.gstexempt,
productpointvalues.earnvalue,
productpointvalues.redeemvalue,
productpointvalues.earnvalue / productpointvalues.redeemvalue AS redemptionmargin
FROM categories, products
LEFT OUTER JOIN productpointvalues USING (productid)
WHERE products.active IS TRUE
AND products.catid = categories.catid
AND products.catid = 2
ORDER BY products.name;
RESULT SET:
productid | cost | srp | margin | type | gstexempt | earnvalue | redeemvalue | redemptionmargin
-----------+-------+--------+----------------------+------+-----------+-----------+-------------+------------------
716 | 8.60 | 10.00 | 14.0000000000000000 | N | f | 50 | 1500 | 0
15 | 87.00 | 100.00 | 13.0000000000000000 | N | f | 500 | 10000 | 0
13 | 26.10 | 30.00 | 13.0000000000000000 | N | f | 150 | 3000 | 0
1189 | 0.00 | 40.00 | 100.0000000000000000 | N | f | 200 | 4000 | 0
14 | 43.50 | 50.00 | 13.0000000000000000 | N | f | 250 | 5000 | 0
My guess is that integer division is to blame: 50 divided by 1500 = 0.03 which rounds to zero. You probably have to cast them to real before doing the division. Naz Gassiep wrote: > Is anyone able to tell me why in the last column of the returned result > set, the value calculated is always 0? > > > QUERY: > > SELECT products.productid, > products.cost, > products.srp, > CASE WHEN products.srp > 0 THEN (products.srp - > products.cost) * 100 / products.srp ELSE 0 END AS margin, > products.type, > products.gstexempt, > productpointvalues.earnvalue, > productpointvalues.redeemvalue, > productpointvalues.earnvalue / > productpointvalues.redeemvalue AS redemptionmargin > FROM categories, products > LEFT OUTER JOIN productpointvalues USING (productid) > WHERE products.active IS TRUE > AND products.catid = categories.catid > AND products.catid = 2 > ORDER BY products.name; > > > > RESULT SET: > > productid | cost | srp | margin | type | gstexempt | > earnvalue | redeemvalue | redemptionmargin > -----------+-------+--------+----------------------+------+-----------+-----------+-------------+------------------ > > 716 | 8.60 | 10.00 | 14.0000000000000000 | N | f > | 50 | 1500 | 0 > 15 | 87.00 | 100.00 | 13.0000000000000000 | N | f > | 500 | 10000 | 0 > 13 | 26.10 | 30.00 | 13.0000000000000000 | N | f > | 150 | 3000 | 0 > 1189 | 0.00 | 40.00 | 100.0000000000000000 | N | f > | 200 | 4000 | 0 > 14 | 43.50 | 50.00 | 13.0000000000000000 | N | f > | 250 | 5000 | 0 > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Indeed. Thanks for that! I keep getting bitten by that too hehe. - Naz. William Garrison wrote: > My guess is that integer division is to blame: 50 divided by 1500 = > 0.03 which rounds to zero. You probably have to cast them to real > before doing the division. > > Naz Gassiep wrote: >> Is anyone able to tell me why in the last column of the returned >> result set, the value calculated is always 0? >> >> >> QUERY: >> >> SELECT products.productid, >> products.cost, >> products.srp, >> CASE WHEN products.srp > 0 THEN (products.srp - >> products.cost) * 100 / products.srp ELSE 0 END AS margin, >> products.type, >> products.gstexempt, >> productpointvalues.earnvalue, >> productpointvalues.redeemvalue, >> productpointvalues.earnvalue / >> productpointvalues.redeemvalue AS redemptionmargin >> FROM categories, products >> LEFT OUTER JOIN productpointvalues USING (productid) >> WHERE products.active IS TRUE >> AND products.catid = categories.catid >> AND products.catid = 2 >> ORDER BY products.name; >> >> >> >> RESULT SET: >> >> productid | cost | srp | margin | type | gstexempt >> | earnvalue | redeemvalue | redemptionmargin >> -----------+-------+--------+----------------------+------+-----------+-----------+-------------+------------------ >> >> 716 | 8.60 | 10.00 | 14.0000000000000000 | N | f >> | 50 | 1500 | 0 >> 15 | 87.00 | 100.00 | 13.0000000000000000 | N | f >> | 500 | 10000 | 0 >> 13 | 26.10 | 30.00 | 13.0000000000000000 | N | f >> | 150 | 3000 | 0 >> 1189 | 0.00 | 40.00 | 100.0000000000000000 | N | f >> | 200 | 4000 | 0 >> 14 | 43.50 | 50.00 | 13.0000000000000000 | N | f >> | 250 | 5000 | 0 >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >