CODEWITHSUNDEEP
sqlsql-server
Mirosław Gądek
Mirosław Gądek

Reputation: 128

I need assistance with my SQL Server query

I'm unable to run a query to get data what I want.

I wrote this code 

SELECT C.topic, C.lastupdate, C.[MONTHN], SUM(CAST(C.Number AS Int)) FROM
(SELECT B.topic, B.lastupdate, B.[MONTHN], COUNT(B.[MONTHN]) as Number FROM
(SELECT A.topic, A.lastupdate, DATENAME(month,lastupdate) as [MONTHN] FROM
(SELECT topic, lastupdate from DACHFAQ) AS A) as B) AS C 
GROUP BY C.topic, C.lastupdate, C.[MONTHN];

but the MS Server is telling that

Column 'B.topic' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't know how to correct this issue.

I Need to get data in such order: Topic (Distinct), Month (Distinct), Sum of updates in that Month

I'm new in SQL thats why I'm asking for help,

Thank you for your comments here I place the solution: like ADyson wrote I needed to add a Groupby clause

SELECT C.topic, C.[MONTHN], SUM(Number) FROM
(SELECT B.topic, B.lastupdate, B.[MONTHN], COUNT(B.[MONTHN]) as Number FROM
(SELECT A.topic, A.lastupdate, DATENAME(month,lastupdate) as [MONTHN] FROM
(SELECT topic, lastupdate from DACHFAQ) AS A) as B
GROUP BY B.topic, B.lastupdate, B.[MONTHN]) as C 
GROUP BY C.topic, C.[MONTHN];

Thank your for your Support!

Upvotes: 0

Views: 55

Answers (1)

John Pasquet
John Pasquet

Reputation: 1842

It's not overly clear what you are wanting, but if what you want is Topic, Month, Total Number of Updates, then this might be what you want:

SELECT Topic, 
    DATENAME(month,lastupdate) as [MONTHN], 
    COUNT(*)
FROM DACHFAQ
GROUP BY Topic, DATENAME(month,lastupdate)

Now, you may need to account for the year as well.

Upvotes: 3

Related Questions