I am attempting to write a bit of SQL that can be used to do a count of Requisition Values, in different value bands, e.g.:
0 - 250
251 - 500
501 - 1000
etc...
I have got this far:
SELECT sum_value
, COUNT(req_id) req_ct
FROM (SELECT prha.requisition_header_id req_id
, CASE SUM(prla.unit_price * prla.quantity)
WHEN 250
THEN '250'
WHEN 500
THEN '500'
WHEN 500
THEN '1000'
ELSE 'OTHER'
END sum_value
FROM po.po_requisition_headers_all prha
, po.po_requisition_lines_all prla
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prha.creation_date >= SYSDATE - 3
GROUP BY prha.requisition_header_id
, prla.requisition_header_id) rt
GROUP BY sum_value;
SUM_V REQ_CT
----- ----------
250 3
500 6
OTHER 726
But - it is not that great, in that it counts REQs with an exact value of 250,400,500 etc.
I am trying to work out the syntax to allow the CASE statement to work with ranges, but I can't work it out.
I have naively attempted this, for example:
WHEN > 250 AND 500
But that errors out, because I can see that the '>' needs to know what it is checking
Any help much appreciated.
Thank you