GROUP BY & aggregate functions

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

Transact-SQL (2000)

GROUP BY & aggregate functions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-11 : 07:32:11

Rolly writes "Dear SQLTeam,

Hope you can help me, I have 3 tables....

Customer Table
==============
CustNo CustName
------ --------
1 Rolly
2 Steven
3 Philip
4 Jessie

Ledger Table
============
CustNo Document EntryNo
------ -------- -------
1 A 1
1 A 2
2 B 3
2 B 4
2 C 5
3 D 6
3 D 7
4 E 8

Amount Table
============
EntryNo Amount
------- --------
1 20
2 30
3 40
4 10
5 20
6 50
7 10
8 20

I want my results to appear as :-

CustNo CustName Sum_Amount
------ -------- ----------
1 Rolly 50
2 Steven 70
3 Philip 60
4 Jessie 20

How can I do this? I can retrieve the CustNo and Sum_Amount, but when I add in the CustName column, SQL Server returns me the error "Column 'CustName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.". Can I get around this without adding in the CustName into GROUP BY? Thanks!

Rolly"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-11 : 07:34:11

You can't get around it, and you don't want to anyway:

SELECT C.CustNo, C.CustName, Sum(A.Amount) AS Sum_Amount
FROM Customer C INNER JOIN Ledger L ON C.CustNo=L.CustNo
INNER JOIN Amount A ON L.EntryNo=A.EntryNo
GROUP BY C.CustNo, C.CustName
ORDER BY C.CustNo

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-11 : 08:06:00

actually, to be honest I prefer doing what you originally intended. i don't like grouping by non-PK columns in tables if you can avoid it.

so, here's how I usually write these:

SELECT C.CustNo, C.CustName, Total
FROM
Customer C
INNER JOIN
(
SELECT L.CustNo, Sum(A.Amount) as Total
FROM Ledger L
INNER JOIN Amount A
ON L.EntryNo=A.EntryNo
GROUP BY L.CustNo
) B
ON C.CustNo = B.CustNo
ORDER BY C.CustNo

lots of times, if people want to return the entire Customer table's columns, i'll see 20 group by's and that's usually a red flag... you aren't aggregating the customer table, but rather the others, so if you use a dervied query and group it by CustNo, you are all set. It logically makes your query a little more clear, and can often perform better as well.

the less columns you group by, the less SQL Server has to do to check for duplicates to perform the groupings.

- Jeff

HendersonToo
Starting Member

20 Posts

Posted - 2004-02-11 : 18:20:24

"Dr Cross Join:" =)

I would lean towards the following expression:


SELECT C.CustNo, C.CustName, ( SELECT SUM(A.Amount) as Total
   FROM Ledger L INNER JOIN Amount A
   ON L.EntryNo = A.EntryNo
   WHERE L.CustNo = C.CustNo) AS TOTAL
FROM Customer C
ORDER BY C.CustNo

Now to me the query plans look like they end up at the same performance, but what's your thoughts on the difference between these two? Granted your solution scales to use more than one aggregrate... Are there any other benefits?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-11 : 18:58:08

I like Rob's better, because AFAIK his would be the only expression that confirms to all the rules required for an indexed view...

DavidM

"SQL-3 is an abomination.."

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-02-11 : 23:20:28

What's the relationship between tables?

Ledger table
custono foreign key references customer(custno)
entryno foreign key references amount (entryno)

Am I right?

smousumi
Starting Member

19 Posts

Posted - 2004-03-10 : 07:44:47

Hii Rolly,

Even i would also suggest u the same as ...

select C.CustNo,C.CustName,
(Select sum(Amount) from Amount A
inner join ledger L
on A.EntryNo=L.EntryNo
where C.CustNo=L.CustNo
group by CustNo
) from Customer C
group by C.CustNo,C.CustName
order by C.CustNo

u can use group by for CustNo as well CustName..

mousumi

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 07:51:35

quote:

Even i would also suggest u the same as ...

select C.CustNo,C.CustName,
(Select sum(Amount) from Amount A
inner join ledger L
on A.EntryNo=L.EntryNo
where C.CustNo=L.CustNo
group by CustNo
) from Customer C
group by C.CustNo,C.CustName
order by C.CustNo

u can use group by for CustNo as well CustName..

??

there's no need for the outer GROUP BY.

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-10 : 09:13:50

quote:
Originally posted by jsmith8858

quote:

Even i would also suggest u the same as ...

select C.CustNo,C.CustName,
(Select sum(Amount) from Amount A
inner join ledger L
on A.EntryNo=L.EntryNo
where C.CustNo=L.CustNo
group by CustNo
) from Customer C
group by C.CustNo,C.CustName
order by C.CustNo

u can use group by for CustNo as well CustName..

??

there's no need for the outer GROUP BY.

- Jeff

...or the inner.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources