Select multiple counts from one database table in one sql command

I have a Oracle database table like so, which records an item being scanned at a scanning point.

ItemsScan

ItemScanId

ItemScanPoint

ItemType

ScanTime

I want to return the ItemScanPoint along with the number of times a specific开发者_如何学运维 ItemType was scanned at that ItemScanPoint.

Something along the lines of..

SELECT ItemScanPoint,
       (SELECT COUNT(*) WHERE ItemType = 1),
       (SELECT COUNT(*) WHERE ItemType = 2)
FROM   ItemsScan

How do I do this in oracle?

What is the most efficient 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

Does this work for you?

select count(ItemScanId), itemscanpoint, itemtype
from itemsscan
where itemtype in (1,2)
group by itemtype, itemscanpoint

Count is a grouping function in oracle. Try this:

select
 ItemType,
 count(ItemType)
from
 ItemsScan
group by
 ItemType

It might be possible to do this using COUNT() as an analytic function as well:

SELECT DISTINCT ItemScanPoint, ItemType
     , COUNT(ItemScanId) OVER (PARTITION BY ItemScanPoint, ItemType)
  FROM ItemsScan

It will return the data in a different format than that asked in the OP but it will return results for all values of ItemType rather than just two.

Hope this helps.