group by clause is not working

Question

SELECT 
    dm.DISTRICT_NAME ,      
    od.REGULAR_WORKERS_COUNT,
    od.DAILY_OR_CASUAL_WORKERS_COUNT,
    od.CONTRACT_WORKERS_COUNT,
    od.TOTAL_COUNT
FROM
    ORG_DETAILS od with (NOLOCK)
INNER JOIN 
    DISTRICT_MASTER dm with (NOLOCK) ON od.DISTRICT_ID = dm.DISTRICT_ID
GROUP BY
    dm.district_name

I had this code, and I am looking to group my table with district_name. I am getting an error though.

Error message :

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

SQL Server is not MySQL. You need to use agg function in every column that is not specified in GROUP BY or GROUP BY multiple columns. http://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql — Lukasz Szozda, Dec 11 '15 at 09:39
It is not "group by clause not working", but you're using it incorrectly. Error text is self-explanatory, read it precisely and think what does it means. — Andrey Korneyev, Dec 11 '15 at 09:40
And stop using `WITH (NOLOCK)`. Based on your misunderstanding of a `GROUP BY` clause, I'm guessing you have don't understand `NOLOCK` either — Mark Sinkinson, Dec 11 '15 at 09:55

score 0 · Answer 1 · edited Dec 11 '15 at 10:18

0

You should try this:-

SELECT 
    dm.DISTRICT_NAME ,      
    COUNT(od.REGULAR_WORKERS_COUNT),
    COUNT(od.DAILY_OR_CASUAL_WORKERS_COUNT),
    COUNT(od.CONTRACT_WORKERS_COUNT),
    COUNT(od.TOTAL_COUNT)
FROM 
    ORG_DETAILS od with (NOLOCK)
INNER JOIN 
    DISTRICT_MASTER dm with (NOLOCK) ON od.DISTRICT_ID = dm.DISTRICT_ID
GROUP BY 
    dm.district_name

edited Dec 11 '15 at 10:18

marc_s

732,580
175
1,330
1,459

answered Dec 11 '15 at 09:45

Ankit Bajpai

13,128
4
25
40

really thanks for your help. i am getting the count ... but . i need the values in the columns to be added when i group two columns of same district_name – thiru Dec 11 '15 at 09:51
i have got it by replacing "count" with "sum"... thank you – thiru Dec 11 '15 at 09:53

score 0 · Answer 2 · edited Dec 11 '15 at 10:18

0

SELECT 
    dm.DISTRICT_NAME ,      
    sum(od.REGULAR_WORKERS_COUNT),
    sum(od.DAILY_OR_CASUAL_WORKERS_COUNT),
    sum(od.CONTRACT_WORKERS_COUNT),
    sum(od.TOTAL_COUNT)
FROM 
    ORG_DETAILS od with (NOLOCK)
INNER JOIN 
    DISTRICT_MASTER dm with (NOLOCK) ON od.DISTRICT_ID = dm.DISTRICT_ID
GROUP BY 
    dm.district_name

edited Dec 11 '15 at 10:18

marc_s

732,580
175
1,330
1,459

answered Dec 11 '15 at 09:54

thiru

173
1
4
16

group by clause is not working

2 Answers2