GROUP BY & SUM help PLEASE - 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

SQL Server 2000 Forums

SQL Server Development (2000)

GROUP BY & SUM help PLEASE

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 09:36:46

Hello,
I am trying to use GROUP BY to calculate all credits, group by student number.
Here is my sql. It doesn't return the correct result.
select stu_number, credit
sum(credit) AS total_credit,
from jen
group by stu_number, credit

stu_num credit
--------------
294 3
292 4
293 4
294 4
296 8
296 18
294 28

Output I want is:
stu_num credit
--------------
294 35
292 4
293 4
296 26

Do you have any idea?

Thanks,
Jenny.

There is no stupid question.
www.single123.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-02 : 09:41:41

this is what you should do:

select stu_num, sum(credit) AS total_credit
from jen
group by stu_num

Go with the flow & have fun! Else fight the flow

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 10:57:47

My table has some more fields. If I added a few more columns, then it does not work correctly.
select stu_num, stu_name, sum(credit) AS total_credit
from jen
group by stu_num, stu_name

When I run it, it first ask for a parameter to enter stu_num, then it display all rows that contain that stu_num.
Ex: if I enter 294 for stu_num, then it display all four rows of 294...
It is supposed to retrieve just one row of the total....
Can you please look into that?
Thanks,
Jenny.

There is no stupid question.
www.single123.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-02 : 11:03:18

well preetyjenny then i'd suggest you post the DML and DDL so we can see the whole picture.
also read up on group by...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-02 : 11:28:59

quote:
Originally posted by prettyjenny

My table has some more fields. If I added a few more columns,
When I run it, it first ask for a parameter to enter stu_num, then it display all rows that contain that stu_num.

Are you using MS Access or SQL Server?
If you want to return 1 line per stu_num, then how can you include/group by the other columns in your table?

i.e., if your data looks like this:

Stu_num | SomeOtherColumn
1234 | A
1234 | B

And you want to display 1 line per Stu_num but also SomeOtherCOlumn as well, which should it pick, "A" or "B" ?

- Jeff

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 11:42:54

Hello, I use both access and sql server 2k.
I am trying to calculate by ROWS, not by COLUMNS.
I want to return more than one line per stu_num. In other words, I want to return all UNIQUE stu_num, then SUM all identified stu_num.
Please look at my sample table and output below:
Do you think it is possible to do to have the output below?

stu_num stu_name credit
----------------------
294 jenny 3
292 amy 4
293 linda 4
294 jenny 4
296 joe 8
296 joe 18
294 jenny 28

Output I want is:
stu_num stu_name credit
----------------------
294 jenny 35
292 amy 4
293 linda 4
296 joe 26

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-03-02 : 11:45:42

SELECT stu_num, stu_name, SUM(credit) As Credit
From MyTable
Group by stu_num,stu_name

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-03-02 : 12:52:25

Yes. I tried that. I return all rows.
Ex: if I enter 294 in the parameter, then it display 4 rows of stu_num 294.
I want just one row for stu_num 294.
Thanks,
Jenny.

There is no stupid question.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-02 : 13:39:35

you need to post the actual SQL of the query you are trying. Cut and paste -- do not give us "some idea" of what you've tried.

Also -- are there multiple stu_name values in your table of each stu_num ? Is your sample data accurate?

You really need to make an effort to give us more information if we can help you; you are being very vague.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources