subqueries help

Question

Post reply

subqueries help

justintime

Ten Centuries

Points: 1060
More actions
September 26, 2011 at 2:53 pm

#244409

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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/

justintime Ten Centuries Points: 1060 More actions · Answer 3

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?

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 4

justintime (9/26/2011)
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?

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

justintime Ten Centuries Points: 1060 More actions · Answer 5

i get this error when running that code.

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'FROM'.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

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/