September 26, 2011 at 2:53 pm
hi. can someone help me figure out why this isn't working? i read the MSDN page on subqueries to no avail. i've seen some examples where the first section that has "max" are used but i'm not sure if i need that. that's not what's tripping this up though, as i get errors even when i run sections 2 and 3 only.
SELECT
RTRIM(bl_id),
max(isnull(count_seats,0))
from (
SELECT
bl_id,
sum(count_seats) count_seats
from(
SELECT
bl_id,
case when rm_type in ('WORKSTATION','OFFICE') then 1 else 0 end count_seats
FROM rooms
) as space
group by bl_id
September 26, 2011 at 3:15 pm
What do you mean by "it isn't working"? Are you getting error message? Is the result not what you expect?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2011 at 3:18 pm
Reading this again, why do you need subqueries at all? What are trying to get out this table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2011 at 5:29 pm
i have a system that tracks people and space. i am creating a stored procedure that takes a snapshot of some of the data on a regular basis. for reasons other than i can get into now, i need to run a query on the space, then a union statement, then the personnel query, which combines my data into one historical table. i am working on building the space portion of the query. if i run what i pasted before, i simply get the error below.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'bl_id'.
if i just run
SELECT
bl_id,
case when rm_type in ('WKSTATION','OFFICE') then 1 else 0 end count_seats
FROM rm
then that works fine, i just need to aggregate my data points. thoughts?
September 26, 2011 at 8:18 pm
justintime (9/26/2011)
if i just runSELECT
bl_id,
case when rm_type in ('WKSTATION','OFFICE') then 1 else 0 end count_seats
FROM rm
then that works fine, i just need to aggregate my data points. thoughts?
Yep...
SELECT bl_id,
count_seats = SUM(CASE WHEN rm_type IN ('WORKSTATION','OFFICE') THEN 1 ELSE 0 END
FROM dbo.rooms
GROUP BY bl_id
ORDER BY count_seats DESC
;
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2011 at 8:27 pm
i get this error when running that code.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
September 27, 2011 at 7:08 am
That is because there is a missing parenthesis to close the sum function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy