Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 28, 2000

Group by Score with IIF()

Question:
What can I use in SQL that does the same thing that IIF(test,TruePart,FalsePart) does in Access?

Answer:
You can achieve the same result using the aggregate function COUNT and the GROUP BY clause. Here's a query that returns the venue names and how many of the venues have rooms available:

SELECT Name, COUNT(*) AS RoomsAvailable 
FROM Venue 
WHERE RoomCapacity >20 
GROUP BY Name
This query works because the WHERE clause is evaluated for those records that have more than 20 rooms and then the results are grouped by name.

Assuming this data set:

Hilton      10
Sheraton    19
Sheraton   200
Marriott     6
Marriott    50
Marriott   300
This will be your result:
Name       RoomsAvailable 
---------- -------------- 
Marriott   2
Sheraton   1

Update:
I received some more information from the original poster of this question on the table structure and the query results they're looking for.

This is a simplified table structure:

venue_id long 
room_id long 
theatre int 
banquet int 
classroom int
with rows:
Venue_id Room_Id Theatre Banquet Classroom 
6        1       20      50      10 
6        2       100     0       0 
7        3       45      7       33 
8        4       30      25      40
The question is: Show me those venues that have any of these:
Theatre > 40 
Banquet > 30 
Classroom > 30"
giving:
Venue_id Theatre Banquet Classroom 
6        1       1       0 
7        1       0       1 
8        0       0       1
With your sample table and data, I see what you're trying to accomplish. You can use the CASE statement in SQL Server, which can act like the IIF. Initially, I coded the query to use CASE statements like this:
SELECT venue_id, 
(CASE WHEN theatre >= 40 THEN 1 ELSE 0 END) AS Theatres,
(CASE WHEN banquet >= 30 THEN 1 ELSE 0 END) AS Banquets,
(CASE WHEN classroom >= 30 THEN 1 ELSE 0 END) AS Classrooms 
FROM Venue
But the result didn't account for multiple venue_id's:
venue_id    Theatres    Banquets    Classrooms 
----------- ----------- ----------- ----------- 
6           0           1           0
6           1           0           0
7           1           0           1
8           0           0           1
So you can use the GROUP BY and COUNT to retrieve the correct result with this query:
SELECT venue_id, 
COUNT((CASE WHEN theatre >= 40 THEN 1 END)) AS Theatres,
COUNT((CASE WHEN banquet >= 30 THEN 1 END)) AS Banquets,
COUNT((CASE WHEN classroom >= 30 THEN 1 END)) AS Classrooms 
FROM Venue
GROUP BY venue_id
Which returns this result:
venue_id    Theatres    Banquets    Classrooms 
----------- ----------- ----------- ----------- 
6           1           1           0
7           1           0           1
8           0           0           1
I believe that will give you the correct results that you're looking for.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date