Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-05-23
5
Medium Priority
?
364 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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