Solved

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

Posted on 2013-05-23
5
359 Views
Last Modified: 2013-05-31
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
Comment
Question by:MIKE
  • 3
5 Comments
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 39191294
Try like this..

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

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39191653
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
 
LVL 32

Expert Comment

by:awking00
ID: 39191718
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
 
LVL 32

Expert Comment

by:awking00
ID: 39191733
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
 
LVL 32

Expert Comment

by:awking00
ID: 39191736
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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