SQLTeam.com | Weblogs | Forums

Add a new row with "Others"

I had an exam ... in SQL.
One question I didn't know is- I have a "Sales" Table and I want to SUM all sales by Product_Key.
I want to SELECT Product_Key, SUM(Sales) from different invoices.
In addition I need to have in the last row of the table, the SUM() of all the "Other" items, all the items for whom the total sales were 1000 or less.
How can I do it?

I wrote this query, but this won't give me the last row of: "Others":

SELECT [Product_Key], SUM([sales])
FROM [dbo].[Fact_Invoice]
GROUP BY [Product_Key]
HAVING SUM([sales]) > 1000

Thank you in advance for answering and helping me progress :slight_smile:

I need to have in the last row of the table
Was that what it said? If so it's meaningless as there's no such thing as a last row in a table.

The question indicates it will need a union of sum sort and you will need a product_key value


SELECT [Product_Key], sales = SUM([sales])
FROM [dbo].[Fact_Invoice]
GROUP BY [Product_Key]
HAVING SUM([sales]) > 1000
union all
select null, sum(Sales)
from
(
SELECT Product_Key, sales = SUM([sales])
FROM [dbo].[Fact_Invoice]
GROUP BY [Product_Key]
HAVING SUM([sales]) <= 1000
) a
order by case when Product_Key is null then 1 else 0 end
1 Like

Thank you very much!
It's a pretty complex query, it worked.