Query to count enties in row and display totals in column

  • Hi everyone,

    I have a table with the following data structure:

    office, asset

    1, desktop

    1, printer

    1, printer

    2, laptop

    2, desktop

    3, desktop

    3, printer

    3, laptop

    3, laptop

    and im trying to form a query so that i get the following output:

    office, desktops, laptops, printers

    1, 1, 0, 2

    2, 1, 1, 0

    3, 1, 1, 2

    I've tried the following but to no avail:

    SELECT

    (

    SELECT Office, Count(Asset)

    FROM stg_AssetSummary

    WHERE Asset= 'Desktop'

    Group By Office

    )As Desktop,

    (

    SELECT Count(Asset)

    FROM stg_AssetSummary

    WHERE Asset= 'Laptop'

    Group By Office

    )As Laptop,

    (

    SELECT Count(Asset)

    FROM stg_AssetSummary

    WHERE Asset= 'Printer'

    Group By Office

    ) As Printer

    Any help would be graciously received!

    --
    I thought what I'd do was, I'd pretend I was one of those deaf-mutes.

  • SELECT office,

    SUM(CASE WHEN asset = 'desktop' THEN 1 ELSE 0 END) AS [Desktop],

    SUM(CASE WHEN asset = 'laptops' THEN 1 ELSE 0 END) AS [Laptops],

    SUM(CASE WHEN asset = 'printers' THEN 1 ELSE 0 END) AS [Printers]

    FROM table_name_here

    GROUP BY office

  • Also

    ;WITH Descktop AS

    (

    SELECT Office, Count(Asset) AS Desktop

    FROM stg_AssetSummary

    WHERE Asset= 'Desktop'

    Group By Office

    ), Laptop AS

    (

    SELECT Count(Asset) AS Laptops

    FROM stg_AssetSummary

    WHERE Asset= 'Laptop'

    Group By Office

    ), Printer AS

    (

    SELECT Count(Asset) AS Printers

    FROM stg_AssetSummary

    WHERE Asset= 'Printer'

    Group By Office

    )

    SELECT D1.Office, COALESCE(D2.Desktop, 0) AS Desktop,

    COALESCE(L.Laptops, 0) AS Laptops,

    COALESCE(P.Printers, 0) AS Printers

    FROM (SELECT DISTINCT Office

    FROM table_name)D1

    LEFT OUTER JOIN Desktops D2

    ON D1.Office = D2.Office

    LEFT OUTER JOIN Laptops L

    ON D1.Office = L.Office

    LEFT OUTER JOIN Printers P

    ON P.Office = D1.Office

  • Also

    SELECT D.Office,

    (SELECT COUNT(*)

    FROM table_name

    WHERE asset = 'desktop'

    AND Office = D.Office) AS 'Desktop'

    ,(SELECT COUNT(*)

    FROM table_name

    WHERE asset = 'laptop'

    AND Office = D.Office) AS 'Laptop'

    ,(SELECT COUNT(*)

    FROM table_name

    WHERE asset = 'printer'

    AND Office = D.Office) AS 'Pesktop'

    FROM (SELECT DISTINCT Office

    FROM table_name) D

  • Thank you for the quick reply 🙂 SUM (CASE...) was what i was looking for and works like a charm, thanks again!

    --
    I thought what I'd do was, I'd pretend I was one of those deaf-mutes.

  • You are welcome.

    Try this also:

    SELECT D.Office, ISNULL(D1.d, 0) AS Desktop

    ,ISNULL(D2.l, 0) AS Laptop

    ,ISNULL(D3.p, 0) AS Printer

    FROM (SELECT DISTINCT Office

    FROM table_name) D

    OUTER APPLY

    (SELECT COUNT(*)

    FROM table_name

    WHERE asset = 'desktop'

    AND Office = D.Office) AS D1(d)

    OUTER APPLY

    (SELECT COUNT(*)

    FROM table_name

    WHERE asset = 'laptop'

    AND Office = D.Office) AS D2(l)

    OUTER APPLY

    (SELECT COUNT(*)

    FROM table_name

    WHERE asset = 'printer'

    AND Office = D.Office) AS D3(p)

  • Hey lee, you may also want to try this

    IF OBJECT_ID('TEMPDB..#OFFICE_ASSETS') IS NOT NULL

    DROP TABLE #OFFICE_ASSETS

    CREATE TABLE #OFFICE_ASSETS

    (

    OFFICE_ID INT,

    ASSET_NAME VARCHAR(100)

    )

    INSERT INTO #OFFICE_ASSETS

    SELECT 1,'desktop'

    UNION ALL

    SELECT 1,'printer'

    UNION ALL

    SELECT 1,'printer'

    UNION ALL

    SELECT 2,'laptop'

    UNION ALL

    SELECT 2,'desktop'

    UNION ALL

    SELECT 3,'desktop'

    UNION ALL

    SELECT 3,'printer'

    UNION ALL

    SELECT 3,'laptop'

    UNION ALL

    SELECT 3,'laptop'

    SELECT * FROM #OFFICE_ASSETS

    SELECT OFFICE_ID , ISNULL (desktop, 0) desktopS ,ISNULL (printer, 0) printer , ISNULL (laptop, 0)laptop

    FROM

    (SELECT OFFICE_ID, ASSET_NAME , COUNT(ASSET_NAME) [COUNT] FROM #OFFICE_ASSETS

    GROUP BY OFFICE_ID, ASSET_NAME ) PIVOT_TABLE

    PIVOT

    ( MAX(PIVOT_TABLE.[COUNT]) FOR PIVOT_TABLE.ASSET_NAME IN ([desktop],[printer],[laptop]) ) PIVOT_HANDLE

    Hope this helps you!

  • thank you thank you for all the replies, it just shows me how many different ways there are to accomplish the same thing 🙂

    --
    I thought what I'd do was, I'd pretend I was one of those deaf-mutes.

  • You're welcome Lee!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply