[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with another query

Posted on 2017-04-14
10
Medium Priority
?
75 Views
Last Modified: 2017-04-15
Hi Experts,

I have the following data.
Location
--------------
LocationID      SchoolName          Address          City                          State      Zip      
9                        Northbrook           172 Church  Northbrook             IL      60078
10                    GlenView
Rooms
------------
RoomID        SchoolID      RoomName      
1                      9              AYM      
2                      9              BL      
3                      9              KS      
4                     10            Room1
5                     10            Room2
With the location details I want the room count for that location.

Output
------------

LocationID      SchoolName          Address          City                          State      Zip                 Room
-------------------------------------------------------------------------------------------------------------------------------
9                        Northbrook           172 Church  Northbrook             IL      60078           3
10                    Glenview                                                                                                     2

Thanks in advance.
0
Comment
Question by:RadhaKrishnaKiJaya
10 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 42093548
Depending on how many records you have in your db, this could be a simple subquery:

SELECT l.LocationId, l.SchoolName, l.Address, l.City, l.[State], l.Zip, (SELECT COUNT(r.RoomID) 
FROM Rooms r 
WHERE r.SchoolId = l.LocationId 
GROUP BY r.RoomId, r.SchoolId) AS Room

Open in new window


You may want to re-think this structure if you have a very large number of records, as subqueries can slow down the performance when it's a large data set.
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 42093551
OK you are using SQL Server but how are you getting the data, Linq to SQL, Entity Framework, or standard ADO.Net?
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093562
Standard ADO.Net
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093635
Hi,

I tried the query you have given. Slightly modified it. Because it was giving error.
select S.LocationID,S.CompanyID,
(SELECT count(R.SchoolID)
  FROM [BeyondGreen].[dbo].[Rooms] R
left outer join Schools on Schools .LocationID = R.SchoolID
group by R.SchoolID) from  Schools S

But still I get the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Thanks in advance.
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 42093641
You shouldn't have to left join Schools in the subquery.  A subquery joins to the main table.  But I had an error in my initial query.

SELECT l.LocationId, l.SchoolName, l.Address, l.City, l.[State], l.Zip, 
(SELECT COUNT(r.RoomID) FROM Rooms r WHERE r.SchoolId = l.LocationId GROUP BY r.RoomId, r.SchoolId) AS Room
FROM Location l  //I was missing this part

Open in new window

0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093666
Still getting the same error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 42093668
Sorry, my bad.  You don't want to group by RoomId.  Just by LocationId.

SELECT l.LocationId, l.SchoolName, l.Address, l.City, l.[State], l.Zip, 
(SELECT COUNT(r.RoomID) FROM Rooms r WHERE r.SchoolId = l.LocationId GROUP BY r.SchoolId) AS Room
FROM Location l 

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 42093737
SELECT l.*,r.Room
  FROM Location l
  JOIN (SELECT SchoolID, COUNT(*) Room FROM Rooms GROUP BY SchoolID) r
    ON l.LocationID = r.SchoolID

Open in new window

1
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42093816
Joining a "derived table" which calculates the required counts, as proposed by Sharath, should be the most efficient approach.

Suggest you avoid "correlated subqueries" in a select clause as these can cause performance issues.

In MS SQL Server, if you really wanted a correlated subquery approach, use an APPLY operator (an OUTER APPLY is used in case there are no rows in the rooms table for a location)
SELECT
      l.LocationId
    , l.SchoolName
    , l.Address
    , l.City
    , l.[State]
    , l.Zip
    , oa.room_count
FROM Location AS l
OUTER APPLY (
      SELECT
            COUNT(*) AS room_count
      FROM Rooms r
      WHERE r.SchoolId = l.LocationId
      ) oa

Open in new window

0
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 42094409
Thanks a lot for your help.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question