CODEWITHSUNDEEP
sqlsql-serverdatabaserelational-database
JasonR
JasonR

Reputation: 409

SQL Query - Help Getting sum of records in a Group

I have the following Tables.

Program Table - 
        prog_ID, 
        prog_Name

Unit Table - 
        unit_ID, 
        unit_name, 
        prog_ID
        max-beds
        max-budgeted-beds
        max-unbudgeted-beds
        max-escalation-beds

Import_Data Table - 
        bed_ID, 
        unit_ID

Bed Table - 
        bed_ID, 
        bed_type_ID

Bed Type Table - 
        bed_type_ID, 
        bed_type

I am trying to write a Query that will give me Total # of beds in each Program.

Example

Program | Bed Count
-------------------
MED        30
REH        25
SUR        36

I have a query that gives me all of the Beds for each Unit but I want to drill it up to the Programs. For some reason I'm just drawing a blank on this.

Thanks in advance for any assistance!

ADDENDUM

I am trying to determine the following as column headers in my output:

Occupied Beds 
--Total Occupied Beds
(SUM(CASE WHEN Bed_Type.bed_type_description = 'Regular' THEN 1 ELSE 0 END)) As 'Total Occupied' 

Budgeted Beds
--OCCUPIED BUDGETED BEDS
(SUM WHEN < MAX_BUDGETED),(CASE WHEN (SUM (CASE WHEN Bed_Type.bed_type_description =     'Regular' THEN 1 ELSE 0 END)) < UNIT.unit_beds_max_budgeted THEN (SUM (CASE WHEN Bed_Type.bed_type_description = 'Regular' THEN 1 ELSE 0 END)) ELSE Unit.unit_beds_max_budgeted END) AS 'Occupied Budgeted Beds'

Unbudgeted Beds
--OCCUPIED UNBUDGETED BEDS
    , (CASE WHEN (SUM (CASE WHEN Bed_Type.bed_type_description = 'Regular' THEN 1 ELSE 0 END)) > UNIT.unit_beds_max_budgeted THEN Unit.unit_beds_max_unbudgeted + Unit.unit_beds_max_budgeted - (SUM (CASE WHEN Bed_Type.bed_type_description = 'Regular' THEN 1 ELSE 0 END)) ELSE 0 END) AS 'Occupied Unbudgeted Beds'

I get an error in here surrounding the reference to 'Unit.unit_beds_max_budgeted' and I'm not sure why. I am using this logic in a different query that works but that output is giving me each Unit like this:

Program  Unit  Occupied_Beds   Budgeted_Beds   Unbudgeted_Beds
---------------------------------------------------------------
MED      A2    44              42              2
MED      A3    34              32              2
CCC      B2    44              42              2
CCC      B3    34              32              2

However the output I am going for is to have MED, and CCC, as one row with the values rolled up (and exclude the Unit Name).

Any help is greatly appreciated, wracking my brain with this and I feel it should be really easy but I can't get it. Am I missing a necessary field to make the link? Thanks in advance.

Upvotes: 0

Views: 73

Answers (2)

TTeeple
TTeeple

Reputation: 2989

A series of joins will get you there. You don't need to join all the way to Bed since Import_Data has the bed_id on it unless you want additional information.

SELECT P.prog_Name AS 'Program', COUNT(I.bed_ID) AS BedCount
FROM Import_Data I
INNER JOIN Unit U
    ON I.unit_ID = U.unit_ID
INNER JOIN Program P
    ON U.prog_ID = P.prog_ID
GROUP BY P.prog_Name

If you want count by bed type

SELECT BT.bed_type AS 'BedType', COUNT(I.bed_ID) AS BedCount
FROM Import_Data I
INNER JOIN Unit U
    ON I.unit_ID = U.unit_ID
INNER JOIN Program P
    ON U.prog_ID = P.prog_ID
INNER JOIN Bed B
    ON I.bed_ID = B.bed_ID
INNER JOIN Bed_Type BT
    ON B.bed_type_ID = BT.bed_type_ID 
GROUP BY BT.bed_type

Upvotes: 3

jcrawfor74
jcrawfor74

Reputation: 1752

select p.prog_ID as Program, Count(*) as BedCount
from Program p
    inner join unit u on p.prog_ID = u.prog_ID
    inner join Import_Data d on u.unit_ID = d.unit_ID
    inner join Bed b on d.bed_ID = b.bed_ID
Group By p.prog_ID

Not running this to test you may need to change count(*) to count(b.bed_ID)

Upvotes: 1

Related Questions