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.
| Author |
Topic |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2005-12-08 : 11:11:19
|
| I have a query that pulls a total by month of roomtypes and puts the totals in a column. I do it for the entire fiscal year even though for this example I only show May and June.Select RoomType, MayNts = Sum((isnull(CountAs,0))*(1-Abs(Sign(DatePart(Month,StayDate)-5)))), JunNts = Sum((isnull(CountAs,0))*(1-Abs(Sign(DatePart(Month,StayDate)-6))))from table1where staydate between '05/01/2005' and '06/01/2005'Group by RoomTypeI want to do a distinct count of room number instead of a sum of countas for each month. So I figured I would simply change the first half of the calculation to be like this...MayNts = Sum((isnull(CountAs,0))*(1-Abs(Sign(DatePart(Month,StayDate)-5)))) would change to be...MayCnt = Coalesce((Count(Distinct RoomNum),0)*(1-Abs(Sign(DatePart(Month,StayDate)-5))))I receive a message stating that StayDate needs to be in the group by clause when I change it this way. Why is it? Does it have something to do with sum vs. count? How would you recommend getting around this?MBeal |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-12-08 : 11:36:35
|
quote:
Originally posted by MBealI receive a message stating that StayDate needs to be in the group by clause when I change it this way. Why is it? Does it have something to do with sum vs. count? How would you recommend getting around this?
The characteristic function expression for the month is outside the aggregatefunction.I'd replace the characteristic function with a case and use the fact that COUNT(DISTINCT) doesn't count NULLs:MayCnt = COUNT(DISTINCT CASE WHEN DATEPART(Month, StayDate) = 5 THEN RoomNum END), |
 |
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2005-12-08 : 11:47:27
|
| That's very close -- thank you. If I have room 1086 that is rented 16 times in January, the query you suggested provides an answer of 1 for the entire month. I need it to show 16.If I had two rooms 1086 and 1087 rented 16 times each, your query provides a total of 2 but it should be 32.I'm going to look at it and see if I can change it slightly to get it to work. If you think of a quick answer, I'll keep checking back to read your reply.Thanks again!MBeal |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-12-08 : 11:55:01
|
| "I want to do a distinct count of room number""If I had two rooms 1086 and 1087 rented 16 times each, your query provides a total of 2 but it should be 32."Which is it? |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2005-12-08 : 12:15:28
|
| Sorry... I do want a distinct count for each room number by day but a total for the month. So sorry for the confusion. Date Room Cnt Share05/01/2005 1086 1 105/02/2005 1086 1 .505/02/2005 1086 1 .505/02/2005 2086 1 1There would be a total of 3 rooms for the monthm 1 for 5/1, 2 for 5/2 even though there are a total of 4 records.Does that clear it up a bit?MBeal |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-12-08 : 16:59:18
|
Ah, my ESP missed the word "day"  Since COUNT(DISTINCT) only works on a single column and you want a count of distinct (StayDate,RoomNum) tuples, it gets a bit messy using COUNT(DISTINCT).The other thing is that 12 COUNT(DISTINCT)s are likely to make for a poor query performance. It's also a bit unnecessary when the values in the distinct counts are coming from disjoint rows. Unless you need other aggregate values that can't be fitted in, I'd go with this sort of thing:SELECT RoomType, MayCnt = COUNT(CASE WHEN DATEPART(Month, StayDate) = 5 THEN 1 END), JunCnt = COUNT(CASE WHEN DATEPART(Month, StayDate) = 6 THEN 1 END)FROM ( SELECT DISTINCT RoomType, StayDate, RoomNum FROM table1 WHERE StayDate >= '20050501' AND StayDate < '20050701' ) AS A |
|
|
|
|
|
|
|
|
Distinct Count