SQL: Returning the sum of items depending on which type it is

Question

I have one field that I need to sum lets say named items However that field can be part of group a or b In the end I need to have all of the items summed for group a and group b

when I say grouped I mean there is a LEFT OUTER JOIN to another table the previous table has a type for the items and the one being joined has a group assigned for this item type

Sorry guys Im a little new to sql I am going to try out what you have given me an get back to you

Ok I feel like we are getting close just not yet allain's I can get them to separate but the issue I need to have both groups to sum on the same row which is difficult because I also have several LEFT OUTER JOIN's involved

Tyler's looks like it might work too so I am trying to hash that out real fast

Alain's seems to be the way to go but I have to tweek it a little more

Answer 1

Maybe I'm not understanding the complexity of what you're asking but... shouldn't this do?

SELECT groupname, SUM(value)
FROM items
WHERE groupname IN ('a', 'b')
GROUP BY groupname

And if you don't care which of a or b the item belongs to then this will do:

SELECT SUM(value)
FROM items
WHERE groupname IN ('a', 'b')

Answer 2

You want something like

SELECT column,SUM( column ) FROM table GROUP BY column

Answer 3

Is that (Tyler's answer) what the question meant, or is it simply this;

SELECT sum(item), groupingField FROM someTable GROUP BY groupingField

or even:

SELECT count(*), item FROM someTable GROUP BY item

which will produce results like this:

sum(item)    |     groupingField
-------------+-----------------------
 71          |          A
 82          |          B

Questioner, perhaps you could clarify which you meant or if I'm oversimplifying?

Answer 4

Try this:

SELECT B.[Group], COUNT(*) AS GroupCount
FROM Table1 A
LEFT JOIN Table2 B ON B.ItemType=A.ItemType
GROUP BY B.[Group]