I've got a Oracle database table like so, which records a product being scanned in a checking point.
I wish to return the ItemScanPoint together with the amount of occasions a particular ItemType was scanned at this ItemScanPoint.
SELECT ItemScanPoint, (SELECT COUNT(*) WHERE ItemType = 1), (SELECT COUNT(*) WHERE ItemType = 2) FROM ItemsScan
How do you do that in oracle?
What's the most effective way?
SELECT ItemScanPoint, SUM(CASE ItemType WHEN 1 THEN 1 ELSE 0 END) , SUM(CASE ItemType WHEN 2 THEN 1 ELSE 0 END) FROM ItemsScan GROUP BY ItemScanPoint
All of this for you personally?
select count(ItemScanId), itemscanpoint, itemtype from itemsscan where itemtype in (1,2) group by itemtype, itemscanpoint
Count is really a grouping function in oracle. Do this:
choose ItemType, count(ItemType) from ItemsScan group by ItemType