using SUM and GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using SUM and GROUP BY

Author

Topic

corboamnesiac
Starting Member

5 Posts

Posted - 2008-03-07 : 15:31:51

suppose i have

colId | col2 | col3 | qtycol
1 toyota camry 1
2 toyota camry 1
3 honda accord 1
4 honda accord 1
5 honda accord 1

how do i return the sum of the qty for each car/model
like this:

1 toyota camry 2
2 toyota camry 2
3 honda accord 3
4 honda accord 3
5 honda accord 3

im having trouble applying SUM & GROUP by because im also selecting colID which is unique.
what do i do!?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 17:19:42

Don't select colid

Poor planning on your part does not constitute an emergency on my part.

corboamnesiac
Starting Member

5 Posts

Posted - 2008-03-07 : 17:40:44

i need to select it.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-07 : 17:44:28

If that is truly what you need you can as follows:

SELECT  a1.colId,
        a1.col2,
        a1.col3,
        a2.totalqty
FROM    YourTableHere a1
        JOIN (SELECT    col2,
                        col3,
                        SUM(qtycol) AS totalqty
              FROM      YourTableHere
              GROUP BY  col2,
                        col3) a2
        ON a1.col2 = a2.col2
           AND a1.col3 = a2.col3

corboamnesiac
Starting Member

5 Posts

Posted - 2008-03-07 : 18:06:59

i get an error trying to execute such query.
does it matter if the total rows returned by that subquery
does not match the outer query. it returns fewer rows since its GROUPING them right?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 18:19:47

What error did you get? What was the error? what was the query you actually ran that produced the error?

We can't help you if you don't provide adequate information.

It seems like you want each row to have the total of all rows in a particular group?
given your exact sample data, and exact requested results...the query posted works.

Poor planning on your part does not constitute an emergency on my part.

corboamnesiac
Starting Member

5 Posts

Posted - 2008-03-07 : 18:57:51

the error message isnt very helpful. it just says it couldnt parse the query. Im using SQL Server Mobile.
im guessing it doesnt support joining by a subquery???

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 19:00:37

Possibly not. Post what you have, if you can..maybe we can tweak it to not require a subquery.

Poor planning on your part does not constitute an emergency on my part.

corboamnesiac
Starting Member

5 Posts

Posted - 2008-03-07 : 19:10:22

SELECT a1.item_id,
a1.car_id,
a2.make,
a2.model,
a3.totalqty
FROM car a1,
(SELECT a.car_id, b.make, b.model, SUM(a.qty) AS totalqty
FROM car_info b, car a
WHERE a.car_id = b.car_id GROUP BY a.car_id, b.make, b.model) a3,
car_info a2
WHERE
a1.car_id = a3.car_id
AND a3.make = a2.make
AND a3.model = a2.model
AND a1.car_id = a2.car_id

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 20:03:11

you have 3 tables there? not 2.

Doesn't look like what was provided.

here is the edit:


 SELECT a1.item_id,
a1.car_id,
a2.make,
a2.model,
a3.totalqty
FROM car a1 (SELECT make, model, SUM(a.qty) AS totalqty
			 FROM car_info  
			 GROUP BY make, model) a2
on
a1.car_id = a2.car_id
AND a1.make = a2.make
AND a1.model = a2.model

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources