Count multiple columns in a query for multiple criteria

Question

I have a query that should count the number of items used by department.

The first two tables give me the units and the persons who used the items.

The third table tells who used what.

STAFF(EMPID,EMPNAME,UNITCTR)

CAFUNIT(UNITCTR, UNITDSC)

CAFTRXHD(BILLNO,TRXDATE,ITEMCODE,ITEMPRICE,ITEMDESC,ITEMPRICE,EMPID)

This is the query

SELECT a.UNITCTR, b.UNITDSC, COUNT(c.ITEMCODE)
FROM UNIT.STAFF a, UNIT.CAFUNIT b, UNIT.CAFTRXHID c
WHERE a.UNITCTR = b.UNITCTR  
  AND c.ITEMCODE IN ('397', '398', '399', '400', '401', '402', '403')
GROUP BY a.UNITCTR, b.UNITDSC 

This returns the count of all used items by department for example Department A used 200 of these items, so I get the Department ID, name and the total count of items.

123|Cafeteria|200

where 200 is the sum of all of these items (397 to 403)

I need to know the count for each item by department for instance Department A used 10 boxes of item 397 and 5 of item B and …

123|Cafeteria|20|20|50|30|50|10|70

Using what is suggested here isn't working or I am not doing it right. Any ideas?

Answer 1

Hello after checking you request, follows a query what I think could help you, I'm using two queries with partition, one to group all items by unit and another to group items by unit and item code.

WITH STAFF  AS (
    SELECT * FROM (
    VALUES
        (1, 'John Smith','U1'), 
        (2, 'David Thompson','U2'), 
        (3, 'Stacey Leigh','U3')
    ) AS _ (EMPID,EMPNAME,UNITCTR)
),CAFUNIT  AS (
    SELECT * FROM (
    VALUES
        ('U1', 'Unit 1'), 
        ('U2', 'Unit 2'), 
        ('U3', 'Unit 3')
    ) AS _ (UNITCTR,UNITDSC)
),CAFTRXHD as (
    SELECT * FROM (
    VALUES
        (1, '2022-01-01','item 1',100,'Item desc 1',1), 
        (2, '2022-02-01','item 2',200,'Item desc 2',2),
        (3, '2022-03-01','item 3',300,'Item desc 3',3),
        (4, '2022-01-01','item 1',100,'Item desc 1',1),
        (5, '2022-01-01','item 2',100,'Item desc 2',1),
        (6, '2022-01-01','item 2',100,'Item desc 2',1),
        (7, '2022-02-01','item 2',200,'Item desc 2',2),
        (8, '2022-02-01','item 2',200,'Item desc 2',2),
        (9, '2022-03-01','item 3',300,'Item desc 3',3),
        (10, '2022-03-01','item 3',300,'Item desc 3',3),
        (11, '2022-03-01','item 3',300,'Item desc 3',3)
    ) AS _ (BILLNO,TRXDATE,ITEMCODE,ITEMPRICE,ITEMDESC,EMPID)
),
--Get all item group by Unit
GetAllByUnit as (
    SELECT 
    t.* FROM
    (
    SELECT 
        IT.UNITCTR,
        IT.UNITDSC,
        (SUM(ITEMPRICE) OVER (partition by IT.UNITCTR order by IT.UNITDSC)) as TotalValue
    FROM 
        CAFTRXHD as HD
        INNER JOIN STAFF as FF ON HD.EMPID = FF.EMPID
        INNER JOIN CAFUNIT as IT ON FF.UNITCTR = IT.UNITCTR
    ) as t
    GROUP BY t.UNITCTR, T.UNITDSC, T.TotalValue
),
--Get all item group by Unit and Item code
GetAllByUnitAndCode as (
    SELECT 
    t.* FROM
    (
    SELECT 
        IT.UNITCTR,
        IT.UNITDSC,
        HD.ITEMCODE,
        (SUM(ITEMPRICE) OVER (partition by IT.UNITCTR,HD.ITEMCODE order by IT.UNITDSC)) as TotalValue
    FROM 
        CAFTRXHD as HD
        INNER JOIN STAFF as FF ON HD.EMPID = FF.EMPID
        INNER JOIN CAFUNIT as IT ON FF.UNITCTR = IT.UNITCTR
    ) as t
    GROUP BY t.UNITCTR, T.UNITDSC, T.ITEMCODE, T.TotalValue
)
SELECT * FROM GetAllByUnit
--SELECT * FROM GetAllByUnitAndCode

The result

UNITCTR  UNITDSC        TotalValue
U1       Unit 1     400
U2       Unit 2     600
U3       Unit 3     1200

Comment the query last line --SELECT * FROM GetAllByUnit and remove the comment over SELECT * FROM GetAllByUnitAndCode

    --SELECT * FROM GetAllByUnit
    SELECT * FROM GetAllByUnitAndCode

The result here is top down not only one line

UNITCTR UNITDSC     ITEMCODE    TotalValue
U1      Unit 1      item 1      200
U1      Unit 1      item 2      200
U2      Unit 2      item 2      600
U3      Unit 3      item 3      1200

Best Regards