Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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
0
MIKE
Asked:
MIKE
  • 3
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
Try like this..

SELECT TOP 3 * FROM( <your original select statement that shows all records> ) as Table1
0
 
Brian CroweCommented:
WITH cteTop3Sales (HospitalSystemGroup, Sales)
AS
(
      SELECT TOP 3 HospitalSystemGroup, SUM(Sales) AS Sales
      FROM MyTable
      GROUP BY HospitalSystemGroup
      ORDER BY SUM(Sales) DESC
)
SELECT HospitalSystemGroup, Sales, (SELECT SUM(Sales) FROM cteTop3Sales) AS GrandTotal
FROM cteTop3Sales
0
 
awking00Commented:
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;
0
 
awking00Commented:
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;
0
 
awking00Commented:
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;
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now