Grouping by primary key on joined table

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)

Grouping by primary key on joined table

Author

Topic

Lambo
Starting Member

1 Post

Posted - 2002-08-16 : 12:51:47

Suppose I have the following tables,

CREATE TABLE Table1 (
Month TinyInt PRIMARY KEY,
AvailableDays TinyInt
)

CREATE TABLE2 (
User VarChar(10)
Month TinyInt
UsedDays TinyInt
CONSTRAINT Table2ProjectMonthKey PRIMARY KEY (User, Month)
)

And I want to do the following:
SELECT T1.Month, SUM(UsedDays) / AvailableDays
FROM Table2 T2
INNER JOIN Table1 T1 ON T1.Month = T2.Month
GROUP BY T1.Month

So basically I want to group by Month and then display the month, and the UsedDays across all Users for that month and divide it by the number of AvailableDays. Since month is a primary key, AvailableDays should have 1 to 1 correspondance with month, but I can't execute the query because SQL states AvailableDays is not in an aggregate function or group by clause.

How would I accomplish this? I can't group by AvailableDays because the numbers aren't unique, the same number might appear more than once in different months.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-08-16 : 13:04:38

SELECT T2.Month, Case T1.AvailableDays
WHEN 0 then 0
ELSE T2.UsedDays/T1.AvailableDays
END
FROM (SELECT Month, sum(UsedDays) UsedDays
FROM Table2
GROUP BY Month) T2 INNER JOIN
Table1 T1
ON T1.Month = T2.Month

I hope this is what you wanted.

Jeremy

r937
Posting Yak Master

112 Posts

Posted - 2002-08-16 : 18:23:52

actually, you can group by month and availabledays

the following three queries should all give you the same results --

select month, availabledays
from table1

select distinct month, availabledays
from table1

select month, availabledays
from table1
group by month, availabledays

the reason these are all the same is the 1=1 relationship of those two columns

so since those are the (only) groups, it wouldn't matter if you inner joined or even cross joined with another table before grouping, because the product rows just "collapse" in the groups --

select table1.month, availabledays
from table1 inner join table2
on table1.month = table2.month
group by month, availabledays

now add the aggregate function --

select table1.month, sum(useddays)/availabledays
from table1 inner join table2
on table1.month = table2.month
group by month, availabledays

should be good to go (well, except for the divide by zero check -- good call, jeremy)

rudy
http://rudy.ca/

rudy
http://rudy.ca/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources