locked
getting count RRS feed

  • Question

  • Hi,

    I have a table PackageItems, which has two columns PackageId and ItemId.

    Each packageId may have multiple ItemIds.

    So

    Packageid ItemId
    1 1
    1 2
    1 3
    2 1
    3 1
    3 3
    4 1
    4 2
    4 3
    ... ...
    ... ...
    ... ...
    ... ...
    ... ...

    I want to know how I can grab the count of packages that contain lets say Both ItemId 1 and ItemId 3?

    Thanks for your help.


    in a never-ending learning journey.




    • Edited by almaverick Thursday, March 12, 2015 3:30 PM
    Thursday, March 12, 2015 2:44 PM

Answers

  • Does this look ok ?
    
     DECLARE @TEMP TABLE (PackageID INT, ItemID INT)
    INSERT INTO @TEMP VALUES (1,1),(1,2),(1,3),(2,1),(3,1),(3,3),(4,1),(4,2),(4,3)
    
    SELECT COUNT(A.PackageID) AS 'COUNT'
    FROM @TEMP A  
    JOIN @TEMP B ON  B.PackageID = A.PackageID 
    WHERE A.ITEMID IN (1) AND B.ITEMID IN (3)
    
    
     

    • Marked as answer by Eric__Zhang Monday, March 23, 2015 6:47 AM
    Thursday, March 12, 2015 3:49 PM
  • Olaf,

    ItemId in (1,3) would grab all the packages that have either 1 or 3.

    Where as i want all the packages that have both 1 and 3 (combined)


    in a never-ending learning journey.

    Olaf's query will work (very efficiently I might add) as long as the same item doesn't repeat within the same package.

    Creating an index on PackageID & ItemID will make it blistering fast...

    If the ItemID can repeat with the same PackageID (aka they are not unique combinations) you will need to something more akin t this...

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp
    GO
    CREATE TABLE #temp (
    	PackageID INT,
    	ItemID INT,
    	PRIMARY KEY (PackageID, ItemID)
    	)
    INSERT #temp (PackageID,ItemID) VALUES (1,1),(1,2),(1,3),(2,1),(3,1),(3,3),(4,1),(4,2),(4,3)
    
    
    SELECT DISTINCT
    	t.PackageID
    FROM 
    	#temp t
    WHERE 1 = 1
    	AND EXISTS (
    		SELECT 1
    		FROM #temp t1
    		WHERE t.PackageID = t1.PackageID
    			AND t1.ItemID = 1
    		)
    	AND EXISTS (
    		SELECT 1
    		FROM #temp t3
    		WHERE t.PackageID = t3.PackageID
    			AND t3.ItemID = 3
    		)

    So... Olaf's solution should be your preferred solution as long as your data supports it. If not, the code above will work but at a higher cost.

    HTH,

    Jason


    Jason Long

    • Marked as answer by Eric__Zhang Monday, March 23, 2015 6:48 AM
    Friday, March 13, 2015 2:02 AM
  • Hi almaverick,

    To achieve your requirement, you can reference the below sample as well.

    DECLARE @T TABLE(PackageID INT,ItemID INT);
    INSERT @T (PackageID,ItemID) VALUES (1,1),(1,2),(1,3),(1,3),(2,1),(3,1),(3,3),(4,1),(4,2),(4,1),(5,1);
    
    SELECT * FROM @T;
    
    SELECT * FROM @T t
    WHERE ItemID = 1 AND EXISTS(SELECT 1 FROM @T WHERE PackageID = t.PackageID AND ItemID =3)
    
    SELECT PackageID FROM @T WHERE ItemID IN (1,3)
    GROUP BY PackageID Having COUNT(DISTINCT ItemID)=2

    If you have any feedback on our support, you can click here.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Thursday, March 19, 2015 1:35 PM
    • Marked as answer by Eric__Zhang Monday, March 23, 2015 6:44 AM
    Friday, March 13, 2015 2:41 AM

All replies

  • SELECT COUNT(PackageId)

    FROM Packages p

    JOIN Packages item1 ON item1.PackageId=p.PackageId AND ItemId=1

    JOIN Packages item3 ON item3.PackageId=p.PackageId AND ItemId=3

    WHERE item1.PackageId IS NOT NULL AND item3.PackageId IS NOT NULL

    Thursday, March 12, 2015 2:56 PM
  • Hello,

    Filter on the ItemID, then Count per package and return only those with a count = 2 (= 2 ItemID)

    SELECT PackageID, COUNT(*) AS CNT
    FROM PackageItems
    WHERE ItemId IN (1, 3)
    GROUP BY PackageID
    HAVING COUNT(*) = 2


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Jason A Long Friday, March 13, 2015 1:41 AM
    Thursday, March 12, 2015 2:59 PM

  • declare @t table (pID int, iID int)
    insert into @t 
    values (1,2),(1,3),(1,4),(2,5),(2,7),(3,12)


    select  iID, count(pID) CountOfPackages
    from @t
    group by iID

    ebro

    • Proposed as answer by ebrolove Wednesday, March 18, 2015 4:25 PM
    Thursday, March 12, 2015 3:00 PM
  • SELECT COUNT(DISTINCT PackageId) AS Count_Of_Packages
    FROM PackageItems
    WHERE ItemId = 1 -- OR ItemId = 3


    A Fan of SSIS, SSRS and SSAS

    Thursday, March 12, 2015 3:04 PM
  • Thanks for your response Greg, this didn't work.

    in a never-ending learning journey.

    Thursday, March 12, 2015 3:27 PM
  • Olaf,

    ItemId in (1,3) would grab all the packages that have either 1 or 3.

    Where as i want all the packages that have both 1 and 3 (combined)


    in a never-ending learning journey.

    Thursday, March 12, 2015 3:29 PM
  • Try this....Select PackageId,Sum(ItemId) As TotalItems Group By PackageId

    Please click "Mark As Answer" if my post helped. Tony C.

    Thursday, March 12, 2015 3:32 PM
  • Olaf,

    ItemId in (1,3) would grab all the packages that have either 1 or 3.

    Where as i want all the packages that have both 1 and 3 (combined)


    in a never-ending learning journey.


    Can a packageID have 2 itemIDs that are both 1, or both 3?
    Thursday, March 12, 2015 3:33 PM
  • SELECT COUNT(DISTINCT PackageId) AS Count_Of_Packages
    FROM PackageItems
    WHERE ItemId = 1 AND ItemId = 3


    A Fan of SSIS, SSRS and SSAS

    Thursday, March 12, 2015 3:35 PM
  • Does this look ok ?
    
     DECLARE @TEMP TABLE (PackageID INT, ItemID INT)
    INSERT INTO @TEMP VALUES (1,1),(1,2),(1,3),(2,1),(3,1),(3,3),(4,1),(4,2),(4,3)
    
    SELECT COUNT(A.PackageID) AS 'COUNT'
    FROM @TEMP A  
    JOIN @TEMP B ON  B.PackageID = A.PackageID 
    WHERE A.ITEMID IN (1) AND B.ITEMID IN (3)
    
    
     

    • Marked as answer by Eric__Zhang Monday, March 23, 2015 6:47 AM
    Thursday, March 12, 2015 3:49 PM
  • Olaf,

    ItemId in (1,3) would grab all the packages that have either 1 or 3.

    Where as i want all the packages that have both 1 and 3 (combined)


    in a never-ending learning journey.

    Olaf's query will work (very efficiently I might add) as long as the same item doesn't repeat within the same package.

    Creating an index on PackageID & ItemID will make it blistering fast...

    If the ItemID can repeat with the same PackageID (aka they are not unique combinations) you will need to something more akin t this...

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp
    GO
    CREATE TABLE #temp (
    	PackageID INT,
    	ItemID INT,
    	PRIMARY KEY (PackageID, ItemID)
    	)
    INSERT #temp (PackageID,ItemID) VALUES (1,1),(1,2),(1,3),(2,1),(3,1),(3,3),(4,1),(4,2),(4,3)
    
    
    SELECT DISTINCT
    	t.PackageID
    FROM 
    	#temp t
    WHERE 1 = 1
    	AND EXISTS (
    		SELECT 1
    		FROM #temp t1
    		WHERE t.PackageID = t1.PackageID
    			AND t1.ItemID = 1
    		)
    	AND EXISTS (
    		SELECT 1
    		FROM #temp t3
    		WHERE t.PackageID = t3.PackageID
    			AND t3.ItemID = 3
    		)

    So... Olaf's solution should be your preferred solution as long as your data supports it. If not, the code above will work but at a higher cost.

    HTH,

    Jason


    Jason Long

    • Marked as answer by Eric__Zhang Monday, March 23, 2015 6:48 AM
    Friday, March 13, 2015 2:02 AM
  • Hi almaverick,

    To achieve your requirement, you can reference the below sample as well.

    DECLARE @T TABLE(PackageID INT,ItemID INT);
    INSERT @T (PackageID,ItemID) VALUES (1,1),(1,2),(1,3),(1,3),(2,1),(3,1),(3,3),(4,1),(4,2),(4,1),(5,1);
    
    SELECT * FROM @T;
    
    SELECT * FROM @T t
    WHERE ItemID = 1 AND EXISTS(SELECT 1 FROM @T WHERE PackageID = t.PackageID AND ItemID =3)
    
    SELECT PackageID FROM @T WHERE ItemID IN (1,3)
    GROUP BY PackageID Having COUNT(DISTINCT ItemID)=2

    If you have any feedback on our support, you can click here.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Thursday, March 19, 2015 1:35 PM
    • Marked as answer by Eric__Zhang Monday, March 23, 2015 6:44 AM
    Friday, March 13, 2015 2:41 AM
  • Where as i want all the packages that have both 1 and 3 (combined)


    Yes, I got that and the clause HAVING COUNT(*) = 2 ensures you get only packages where both id's exists.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, March 13, 2015 7:56 AM