user3127648 user3127648 - 1 year ago 43
SQL Question

Group By with count doesn't work

I am trying to use group by with count function but this doesn't work.

SELECT
projects.AgencyId,
projects.ProgramId,
count(projects.ProjStatusByMin) as status,
projects.ProjStatusByMin
from
projects
where
projects.AgencyId=40
group by
projects.ProjStatusByMin


This above code works in MySQL perfectly now i want to achieve the same thing in SQL Server.

Select
ProjStatusByMin,
COUNT(ProjStatusByMin) [projstatus]
from
Projects
where
AgencyId=40
group by
ProjStatusByMin,AgencyId,ProjId


However if i select a single column like the bellow code then group by work

Select
ProjStatusByMin,
COUNT(ProjStatusByMin) [projstatus]
from
Projects
where
AgencyId=40
group by
ProjStatusByMin


Now how can I achieve group by with multi column selected?

Answer Source

You have to add all the columns (except for the one in the count function) to the group by clause.

SELECT projects.AgencyId,projects.ProgramId,count(projects.ProjStatusByMin) as status,
projects.ProjStatusByMin 
from projects 
where projects.AgencyId=40 
group by projects.ProjStatusByMin, projects.AgencyId,projects.ProgramId