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      10Sheraton    19Sheraton   200Marriott     6Marriott    50Marriott   300

This will be your result:

Name       RoomsAvailable ---------- -------------- Marriott   2Sheraton   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           06           1           0           07           1           0           18           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 VenueGROUP BY venue_id

Which returns this result:

venue_id    Theatres    Banquets    Classrooms ----------- ----------- ----------- ----------- 6           1           1           07           1           0           18           0           0           1

I believe that will give you the correct results that you’re looking for.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a