Solved

help with SQL

Posted on 2015-01-09
15
134 Views
Last Modified: 2015-01-09
I have two tables; a house table and a bed table.    The house table has a long value that is the number of total beds in the house.  The bed table has record for each bed that included a description, type, etc., and a status that indicates if the bed is vacant or occupied.  What I'm trying to do in one query is return all houses that have at least 1 open bed, with columns for total beds, occupied beds and vacant beds.  I don't know how to combine the JOIN with one or more aggregate functions that give a count of bed records with a status of vacant and/or occupied, or one aggregate function that give the number of vacant beds along with a calculation of: occupied bed = total beds - vacant beds.

Thanks
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
15 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540328
Can you post the structure of your tables please.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 40540354
here ya go:

 

housetable.pngbedtable.png
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 40540356
bedtable.png
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540371
Something like:

with myTable as (
Select H.ID, 
(Select Count(*) from dbo.resource_home_bed as T where H.ID=T.FK_resource_home and T.FK_bed_status = "Open") as OpenBeds
from dbo.resource_home H
)
Select * from MyTable
Where OpenBeds>=1

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 40540396
Thanks.  I also need the number_of_beds from the home table.  I'm unfamiliar with the WITH syntax.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540405
with myTable as (
Select H.ID, sum(H.number_of_beds) as number_of_beds, 
(Select Count(*) from dbo.resource_home_bed as T where H.ID=T.FK_resource_home and T.FK_bed_status = "Open") as OpenBeds
from dbo.resource_home H
)
Select * from MyTable
Where OpenBeds>=1

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40540406
I'm assuming you are using SQL Server.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 40540417
Ah, like this, right.

with myTable as (
Select H.ID,
(Select Count(*) from dbo.resource_home_bed as T where H.ID=T.FK_resource_home and T.FK_bed_status = "Open") as OpenBeds, H.number_of_beds
from dbo.resource_home H
)
Select * from MyTable
Where OpenBeds>=1
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 40540418
How do I subtract OPenBeds from Total beds to give a column names occupied beds?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40540426
Do it in the last two lines

with myTable as (
Select H.ID, sum(H.number_of_beds) as number_of_beds, 
(Select Count(*) from dbo.resource_home_bed as T where H.ID=T.FK_resource_home and T.FK_bed_status = "Open") as OpenBeds
from dbo.resource_home H
)
Select *,  number_of_beds - OpenBeds as OccupiedBeds 
from MyTable
Where OpenBeds>=1

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 40540431
I got this to work; is there a better way without having the Subquery clause specified twice?

with myTable as (
Select H.ID,
h.number_of_beds, (Select Count(*) from dbo.resource_home_bed as T where H.ID=T.FK_resource_home and T.FK_bed_status = 2) as OpenBeds,
  (h.number_of_beds - (Select Count(*) from dbo.resource_home_bed as T where H.ID=T.FK_resource_home and T.FK_bed_status = 2)) as OccupiedBeds

from dbo.resource_home H
)
Select * from MyTable
Where OpenBeds>=1;
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 40540443
got it! Thanks so much!
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 40540444
Excellent!   Thanks so much!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40541441
>>" I'm unfamiliar with the WITH syntax."
WITH is used with "Common Table Expressions" (CTE)
These are extremely useful devices for "recursion" (e.g. in a hierarchy) or to avoid repeating a subquery over and over. In my view they should be used only for recursion or to avoid repetition.

It isn't required that you use WITH for your query. I wouldn't in this particular case as there is no benefit in doing so. All that is required is "nesting" the query (sometimes referred to as a "derived table") like this:
SELECT
      ID
    , number_of_beds
    , number_of_beds - OpenBeds AS OccupiedBeds
FROM (
            SELECT
                  H.ID
                , H.number_of_beds
                , (
                        SELECT
                              COUNT(*)
                        FROM dbo.resource_home_bed AS T
                        WHERE H.ID = T.FK_resource_home
                        AND T.FK_bed_status = 2
                  ) AS OpenBeds
            FROM dbo.resource_home H
      ) AS DERIVED
WHERE OpenBeds >= 1
;

Open in new window

In SQL Server there is also "cross apply" which is super handy for this:
SELECT
      H.ID
    , H.number_of_beds
    , CA.OpenBeds
    , H.number_of_beds - CA.OpenBeds AS OccupiedBeds
FROM dbo.resource_home H
      CROSS APPLY (
            SELECT
                  COUNT(*) AS OpenBeds
            FROM dbo.resource_home_bed AS T
            WHERE H.ID = T.FK_resource_home
            AND T.FK_bed_status = 2
      ) AS CA
;

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40541559
@Phillip Burton

Congratulations on achieving Rookie of the Year for 2014.
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

624 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