user3127648 user3127648 - 7 months ago 8
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

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