Solved

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

Posted on 2013-05-23
5
361 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
[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
  • 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

724 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