Query help on sales transaction table

I have a table that contains transaction level sales data. I am trying to satisfy a reporting request as efficiently as possible which I don’t think I am succeeding at right now. Here is some test data:

DROP TABLE IF EXISTS TMP_SALES_DATA;
CREATE TABLE TMP_SALES_DATA ([DATE] DATE, [ITEM] INT, [STORE] CHAR(6), [TRANS] INT, [SALES] DECIMAL(8,2));
INSERT INTO TMP_SALES_DATA
VALUES
('9-29-2020',101,'Store1',123,1.00),
('9-29-2020',102,'Store1',123,2.00),
('9-29-2020',103,'Store1',123,3.00),
('9-29-2020',101,'Store1',124,1.00),
('9-29-2020',101,'Store1',125,1.00),
('9-29-2020',103,'Store1',125,3.00),
('9-29-2020',102,'Store1',126,2.00),
('9-29-2020',101,'Store2',88,1.00),
('9-29-2020',102,'Store2',88,2.00),
('9-29-2020',103,'Store2',88,3.00),
('9-29-2020',101,'Store2',89,1.00),
('9-29-2020',101,'Store2',90,1.00),
('9-29-2020',102,'Store2',91,2.00),
('9-29-2020',103,'Store2',91,3.00),
('9-29-2020',101,'Store3',77,1.00);

And I need to represent both individual item sales as well as total transaction sales for every transaction in which the specified items were present. Examples:

-- Item sales
SELECT [ITEM], SUM([SALES]) AS [SALES]
FROM TMP_SALES_DATA
WHERE [ITEM] IN (101,103) AND [STORE] IN ('Store1','Store2' ,'Store3') AND [DATE] = '9-29-2020'
GROUP BY [ITEM]

Returns this:

ITEM    SALES
101     7.00
103     12.00

And I can get the total transaction sales in which a single item was present this way:

-- Total transaction sales in which ITEM 101 exists
SELECT SUM(S1.[SALES]) AS [TTL_TRANS_SALES]
FROM TMP_SALES_DATA S1
WHERE EXISTS (SELECT 1 FROM TMP_SALES_DATA S2 WHERE S2.[DATE]=S1.[DATE] AND S2.[STORE]=S1.[STORE] AND S2.[TRANS]=S1.[TRANS] AND S2.[ITEM]=101 AND S2.[STORE] IN ('Store1','Store2','Store3') AND S2.[DATE] = '9-29-2020')

-- Total transaction sales in which ITEM 103 exists
SELECT SUM(S1.[SALES]) AS [TTL_TRANS_SALES]
FROM TMP_SALES_DATA S1
WHERE EXISTS (SELECT 1 FROM TMP_SALES_DATA S2 WHERE S2.[DATE]=S1.[DATE] AND S2.[STORE]=S1.[STORE] AND S2.[TRANS]=S1.[TRANS] AND S2.[ITEM]=103 AND S2.[STORE] IN ('Store1','Store2','Store3') AND S2.[DATE] = '9-29-2020')

But I am failing to find a clean, efficient, and dynamic way to return it all in one query. The end user will be able to specify the items/stores/dates for this report. The end result I would like to see is this:

ITEM    SALES   TTL_TRANS_SALES
101     7.00    20.00
103    12.00    21.00

Answer

If I understand correctly, you can use window functions to summarize by transaction and then aggregate:

select item, sum(sales), sum(trans_sale)
from (select ts.*, sum(sales) over (partition by trans) as trans_sale
      from tmp_sales_data ts
     ) ts
group by item;

Here is a db<>fiddle.

You can add appropriate filtering in the subquery.

Advertisement

Answer