how to use GROUP BY based on 2 specific columns

I have a table in SQL Server which looks like this :

each ticket id can have multiple order issue. On each row I need to calculate ticket full revenue as below : SUM(revenue) of all items within the same ticket id

So in my case, ticket id has 3 items (10,20,30) so the ticket full revenue = 30+5+15 = 50 we add it in each row for that specific ticket id. it doesn’t matter if the ticket id has multiple order issue, it’s counted only once.

I am struggling to make it work. Here is my query

Select [ticket id], [order issue], item, qty, [unit price], 
qty*[unit price] AS revenue, 
SUM(qty*[unit price]) OVER (PARTITION BY [ticket id]) AS [ticket full revenue]
From orders
Group by [ticket id], [order issue], item, qty, [unit price]
Order by [ticket id]

The query returns 100 as it’s counting the revenue for the order issue ’email not provided’ and ‘adress not provide’. but my logic it should count only once the same ticket id I tried to adjust by dividing the value by count distinct value of concat [ticket id] and [order issue] but still not working.

(SUM(qty*[unit price]) OVER (PARTITION BY [ticket id])) / (Count(distinct concat([ticket id], [order issue]))) AS [ticket full revenue]

any suggestion please what I am doing wrong ? Thank you.

Answer

SELECT [ticket id], [order issue], item, qty, [unit price], 
(SELECT SUM(item) FROM orders as B WHERE b.ticket_id = a.ticket_id GROUP BY b.ticket_id) as 'ticket full revenue'
FROM orders A

Advertisement

Answer