I've got a Oracle database table like so, which records a product being scanned in a checking point.

ItemsScan
ItemScanId
ItemScanPoint
ItemType
ScanTime

I wish to return the ItemScanPoint together with the amount of occasions a particular ItemType was scanned at this ItemScanPoint.

Something like..

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