CODEWITHSUNDEEP
sqlsqlite
relima
relima

Reputation: 3423

retrieve summed amounts from sql

What is the problem with the following query:

SELECT wbCode||yr, CAST(coalesce(Cons,0) AS FLOAT), wbCode
FROM Commodities
Where CCode in (2611,2513,2961) Group by wbCode||yr

I want the query above to return the sums for every wbCode in a given yr (USA1990), for example; but the code above is not summing up; it is retrieving the same data as:

SELECT wbCode||yr, CAST(coalesce(Cons,0) AS FLOAT), wbCode 
FROM Commodities
Where CCode in (2611) Group by wbCode||yr

SELECT wbCode||yr, CAST(coalesce(Cons,0) AS FLOAT), wbCode 
FROM Commodities Where CCode in (2513,2961)
Group by wbCode||yr

How can I get it to sum?

Thank you so much for your help.

Upvotes: 0

Views: 47

Answers (3)

Martijn
Martijn

Reputation: 13622

How about this:

  SELECT wbCode
  ,      yr
  ,      SUM(CAST(coalesce(Cons,0) AS FLOAT))
    FROM Commodities
   WHERE CCode in (2611,2513,2961) 
GROUP BY wbCode
,        yr;

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

Why aren't you using SUM function to aggregate results?

Upvotes: 1

gbn
gbn

Reputation: 432180

Why || ? Just group by them separately

SELECT wbCode, yr, SUM(CAST(coalesce(Cons,0) AS FLOAT)), wbCode
FROM Commodities
Where CCode in (2611,2513,2961)
Group by wbCode, yr

Or

SELECT wbCode||yr, SUM(CAST(coalesce(Cons,0) AS FLOAT)), wbCode
FROM Commodities
Where CCode in (2611,2513,2961)
Group by wbCode, yr

Upvotes: 3

Related Questions