Solved

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

Posted on 2013-05-23
5
360 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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