How to use Aggregate function with mutliple tables

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 2005 Forums

Other SQL Server Topics (2005)

How to use Aggregate function with mutliple tables

Author

Topic

jamlogg
Starting Member

10 Posts

Posted - 2011-06-22 : 10:19:10

I trying to write this simple query to calculate a summary, but it seems like the query is giving me an error. Does somebody know what the correct syntax should be? Thanks in advance!

SELECT DISTINCT table1.col1, table2.col1,
SUM(table1.col1* table2.col2) as summmary
from table1,table2
where table1.ID = table2.ID
GROUP by table1.col1
order by summary asc

K.G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-22 : 10:23:24

The error will be about the ORDER BY?
Try this:
order by SUM(table1.col1* table2.col2)

or use a derived table.

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-22 : 11:05:52

it's the grouping.

Change you GROUP BY to:

GROUP by table1.col1, table2.col1

Also, the distinct keyword is unecessary in this query

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-22 : 11:07:41

also, it would be a very good idea to learn proper join syntax

jamlogg
Starting Member

10 Posts

Posted - 2011-06-22 : 16:54:42

Thanks for the reply, but it didnt work :(

quote:
Originally posted by webfred

The error will be about the ORDER BY?
Try this:
order by SUM(table1.col1* table2.col2)

or use a derived table.

No, you're never too old to Yak'n'Roll if you're too young to die.

K.G

jamlogg
Starting Member

10 Posts

Posted - 2011-06-22 : 16:56:17

I changed the group by as you mentioned but that didnt work either. I am using a DB2 database, do you think that could be a reason why?

quote:
Originally posted by russell

it's the grouping.

Change you GROUP BY to:

GROUP by table1.col1, table2.col1

Also, the distinct keyword is unecessary in this query

K.G

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-06-22 : 17:23:14

The syntax is fairly standard - but yeah, DB2 is going to be different. Can't really say for sure...

However, there is no reason to use DISTINCT and GROUP BY in the same query. The GROUP BY is going to give you distinct groupings anyway - so all that really does is make the query run longer and perform extra work.

Also, instead of making us guess what error you are getting - it would help a lot if we saw the actual error. Russell assumed the error was related to the GROUP BY because you need to specify all non-aggregated columns.

I would also remove the ORDER BY - at least until you have verified the results are correct and what you expect. Normally, sorting the results is left to the calling system.

Jeff

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-06-23 : 05:25:54

Try this first - just to see that you have the aggregation syntax proper for DB2 (I've 20+ years of experience on DB2 and it looks good). It's very unusual to have the drivers of a grouping involved within the calculation of a grouping itself - I can't see it making much sense.

SELECT
table1.col1, table2.col1,
count(*) as abc
from table1, table2
where table1.ID = table2.ID
GROUP by table1.col1, table1.col2

if this works - then I think you have a problem with the "maths" part.
this may work also.

select a, b, a*b
from
(
SELECT
table1.col1 as a, table2.col1 as b,
count(*) as abc
from table1, table2
where table1.ID = table2.ID
GROUP by table1.col1, table1.col2
) inlinetab
order by 1,2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources