tricky grouping? - 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

Other Forums

MS Access

tricky grouping?

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-07-28 : 05:35:11

Hi,

Delegation - Days

NULL - 10
A - 5
B - 10
C - 5

i want the sum of delegation days/month which are not null and sum of days where delegation is null
= {Null} 10 days, {A,B,C} 20 days

the logic shoul dbe sth like this:

SELECT Count(in.[NumberofDays]) AS NbOfDaysINDelegation, Count(out.[NumberofDays]) AS NbOfDaysOUTDelegation, [Overview].[TravelMonth]
FROM in, out
WHERE in = SELECT Delegation, NberofDays FROM Overview WHERE Delegation IS NULL AND [Overview].[TravelYear]=Year,
out = SELECT Delegation, NberofDays FROM Overview WHERE Delegation IS NULL AND [Overview].[TravelYear]=Year,
GROUP BY [Overview].[TravelMonth];

but howis the right syntax???

thanks for your help!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-28 : 08:22:41

I may not have it right in Access, but here's a try...

Select
sum(DelegationIn),
sum(DelegationOut)
From (Select DelegationIn = iif(isnull(Delegation),days,0), DelegationOut = iif( not isnull(Delegation),days,0) From Overview)

or maybe (I've never used/seen this syntax, I am jusr mimicking your example)

Select
sum(DelegationIn),
sum(DelegationOut)
From OverviewPrep
Where OverviewPrep = (Select DelegationIn = iif(isnull(Delegation),days,0), DelegationOut = iif( not isnull(Delegation),days,0) From Overview)

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources