Distinct or group by date

Question

Hi Expert,

how to distinct or group by below records

create table main5 (unit int, saledate date ,data int)

insert main5

values(1,'20210407',10),
(1,'20210407',20),
(1,'20210407',30)

Expected output

i tried

select distinct sum(unit),saledate,data from main5 group by (unit),saledate,data
but not getting proper data

Answer 1

Hi @Shambhu Rai
You can just use simple GROUP BY with aggregate function(SUM) like this:

SELECT unit,saledate,SUM(data) AS SUM_data  
FROM #main5  
GROUP BY unit,saledate  

Or use window function OVER( PARTITION BY ...) like this:

SELECT DISTINCT unit,saledate,SUM(data)OVER(PARTITION BY unit,saledate) AS SUM_data  
FROM #main5  

PARTITION BY does not affect the number of rows returned, but it changes how a window function's result is calculated.And that's why we need to add ‘DISTINCT’in this query.
The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window.
You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

In this issue,I tend to recommend using 'GROUP BY'

Best regards,
LiHong

Answer 2

i got this

SELECT distinct saledate, --sum(data) as data,
sum(data) OVER(PARTITION BY unit ) AS ROWNO ,unit
FROM main5 group by saledate,unit,data

Answer 3

Hi @LiHong-MSFT I'm following this thread and can I ask what if I want to group that by YYYY-MM only and not YYYY-MM-DD?

Answer 4

@Sherlan Emmanuel Burgonia - one way to sum by year and month would be

SELECT unit,Year(saledate) As Year,Month(saledate) AS Month, SUM(data) AS SUM_data  
FROM #main5  
GROUP BY unit,Year(saledate),Month(saledate)  

Tom

P.S. When you have a question related to some topic, it is best to create a new thread, avoid asking a new question in a current thread. That can make things confusing. If you think it would be helpful, you can include a link to the old thread in your new question.