CODEWITHSUNDEEP
sqlsql-server
squidge
squidge

Reputation: 454

SQL Sum of rows grouped by id

This SQL:

select Name,
       (select COUNT(1) from tbl_projects where statusId = tbl_sections.StatusId) as N
from tbl_sections
left join tbl_section_names on tbl_section_names.Id = NameId

Generates the follows data:

Name    N
Completed   133
Cancelled   100
Unassigned  1
Sales   49
Development 10
Development 4
Development 1

I'm trying to modify it so it returns the data as follows:

Name    N
Completed   133
Cancelled   100
Unassigned  1
Sales   49
Development 15

(ie, sum up the rows where the name is the same)

Can anyone suggest some clues on how to make this work ? I'm guessing I need a SUM and a GROUP BY, but it never even runs the query as all I get are errors.

Upvotes: 2

Views: 176

Answers (5)

JustinDanielson
JustinDanielson

Reputation: 3185

Try and give this a go.

select Name,
   SUM(select COUNT(1) from tbl_projects where statusId = tbl_sections.StatusId) as N
from tbl_sections
left join tbl_section_names on tbl_section_names.Id = NameId
group by Name

Upvotes: 0

AaronLS
AaronLS

Reputation: 38367

This query is giving you count per status, which means Development has sections with three different status's, and the query would reflect this and make more sense if you added the status as a column:

select Name, tbl_sections.StatusId,
       (select COUNT(1) from tbl_projects where statusId = tbl_sections.StatusId) as N
from tbl_sections
left join tbl_section_names on tbl_section_names.Id = NameId

I don't know the structure of your database, but I if you want a count of the number of sections per name, might be like this. This basically will look at the result of the join, and then summarize it by telling you the number of times each unique name occurs:

select Name, count(*)
from tbl_sections
left join tbl_section_names on tbl_section_names.Id = NameId
Group By Name

Upvotes: 0

rs.
rs.

Reputation: 27427

Try this

select Name, count(p.statusid) N
from tbl_sections
left join tbl_section_names on tbl_section_names.Id = NameId
left outer join tbl_projects p on tbl_sections.StatusId = p.statusId 
group by Name

Upvotes: 1

PRNDL Development Studios
PRNDL Development Studios

Reputation: 3300

Try this query. It sums N grouped by Name.

SELECT Name, SUM(N) 
FROM (
    SELECT Name,
    (SELECT COUNT(1) 
        FROM tbl_projects 
        WHERE statusId = tbl_sections.StatusId
    ) AS N
    FROM tbl_sections
    LEFT JOIN tbl_section_names ON tbl_section_names.Id = NameId
) a
GROUP BY a.Name

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Select Name, Sum(N) from
    (select Name,
       (select COUNT(1) from tbl_projects where statusId = tbl_sections.StatusId) as N
    from tbl_sections
    left join tbl_section_names on tbl_section_names.Id = NameId)
group by Name

Upvotes: 0

Related Questions