CODEWITHSUNDEEP
sqlsql-serverfunction
Kam Par
Kam Par

Reputation: 91

compute the `SUM(Count(id))`

To compute the SUM(Count(id)) i used this query.I have already read this

Select  *,SUM(Solders) as sums

From
(SELECT   
       Groups.[ID] 
      , Groups.[Name] 
      ,Count( Solders.ID)   as Solders

  FROM  Groups join Solders on Groups.ID=Solders.GID 


       group by   Groups.[ID],Groups.[Name]
   )B

but this error happen:

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

Upvotes: 0

Views: 1374

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Use window functions instead:

SELECT g.[ID], g.[Name], Count(s.ID) as Solders,
       SUM(Count(s.Id)) OVER () as SumCount
FROM Groups g join
     Solders s
     on g.ID = s.GID 
GROUP BY g.[ID], g.[Name];

Upvotes: 1

Hogan
Hogan

Reputation: 70538

This is a standard group by it looks like this:

SELECT ID, NAME,SUM(Solders) as sums
FROM
  (SELECT   
     Groups.[ID] 
     , Groups.[Name] 
     ,Count( Solders.ID)   as Solders
   FROM  Groups join Solders on Groups.ID=Solders.GID 
   GROUP BY Groups.[ID],Groups.[Name]
  )B
GROUP BY ID, NAME

Upvotes: 0

Related Questions