asked on

How to use a Select Statement, within a Select Statement as a COLUMN of data?

I need assistance with using the SUM Function in SQL Server 2012.

I believe I'll need a "Select Statement as a Column" within a Select Statement, but not sure..??

My data looks like this:

HospitalID      Sales      HospitalSystemGroup
1000      10000.00      ABC Hospitals
2000      20000.00      DEF Hospitals
3000      30000.00      ABC Hospitals
4000      40000.00      GHI Hospitals

I need the syntax to accomplish this Top 3 based on Sales:

Sales      HospitalSystemGroup      GrandTotal
40000.00      ABC Hospitals      100000.00
20000.00      DEF Hospitals      100000.00
40000.00      GHI Hospitals      100000.00

I would like the GRAND Total to appear on EACH ROW of data.

The main issue is that I'm using a "TOP 3" limitation in my final display SQL, which is really what's causing my issue as under normal circumstances I could simply use the SUM() function, but when I do that it only sums for the Top 3 and not the ENTIRE list. I need TOP 3 shown with the SUM for the ENTIRE list, not just the Top 3

Ron Malmstead

Try like this..

SELECT TOP 3 * FROM( <your original select statement that shows all records> ) as Table1

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

select y.sales, y.hospitalsystemgroup, x.grandtotal from
(select distinct sum(sales) over (order by 1) grandtotal, hospitalsystemgroup
from hospitals) as x
left join
(select sum(sales) sales, hospitalsystemgroup
from hospitals
group by hospitalsystemgroup) as y
on x.hospitalsystemgroup = y.hospitalsystemgroup
order by y.hospitalsystemgroup;

awking00

To do as select within a select -
select sum(sales) sales, hospitalsystemgroup,
(select sum(sales) from hospitals)
from hospitals
group by hospitalsystemgroup
order by hospitalsystemgroup;

awking00

Forgot alias for grandtotal -
select sum(sales) as sales, hospitalsystemgroup,
(select sum(sales) from hospitals) as grandtotal
from hospitals
group by hospitalsystemgroup
order by hospitalsystemgroup;