question

sqlLearner 1 avatar image
sqlLearner 1 asked

combine rows that are similiar

0 I am trying to combine the same rows into one. I have multiple rows of the same date and I want it to become one row of each date. select COUNT (distinct datefield) AS Cnt, year(datefield) * 100 + datepart(M, datefield) as YearMnth FROM table1 WHERE ID = 123 Group BY year(datefield) * 100 + datepart(M,datefield) Order BY yearmnth It counting all the dates in the month even when they occur more than once. I want it to count each date only once.
sqlaggregates
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
why not just SELECT DISTINCT 1, year(datefield)*100+datepart(M,datefield) as YearMnth FROM table1 WHERE ID=123 ORDER BY 2 ?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.