Is this possible? Select Statement difficulties

Question

Post reply

Is this possible? Select Statement difficulties

Viewing 9 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

Try to make all the four separate select statement in to one statement by using UNION operation. Like

Select 'Savings per Policy' as TYPE , subpolicy_name, SUM(CO2_savings) As total_savings

From #CO2temp

group by subpolicy_name

union all

Select 'Savings per Building',building_name, SUM(CO2_savings)

From #CO2temp

group by building_name

union all

Select 'Savings per Floor',floor_name, SUM(CO2_savings)

From #CO2temp

group by floor_name

union all

Select 'Savings per Room',room_name, SUM(CO2_savings)

From #CO2temp

group by room_name

nialltuohy Right there with Babe Points: 766 More actions · Answer 2

Thanks for the post appreciate it, unfortunately that doesn't solve my problem, Im stuck using MS chart Controls which don't seem to be able to process multiple sets. I tried your code and chart controls cant find 'building_name' same problem i've had for a few weeks.

Ive tried this code and it works fine with the chart controls but my only problem is Im not sure is it possible to SUM my totals,

Select

subpolicy_name, SUM(CO2_savings) As subpolicy_savings,

building_name, SUM(CO2_savings) As building_savings,

floor_name, SUM(CO2_savings) As floor_savings,

room_name, SUM(CO2_savings) As room_savings

From #CO2temp

group by subpolicy_name, building_name, floor_name, room_name

Order by subpolicy_name, building_name, floor_name, room_name

What i mean by this is that for example on the subpolicy_name my results return

Exempt 23.4

Finance 28.08

Finance 23.4

i need a total here, i need to have my results as

Exempt 46.8

Finance 51.48

Thanks again for the response

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Something like this?

/** Getting Total Savings per Policy*/

Select 'Policy' AS Sector,

subpolicy_name,

'' AS building_name,

'' AS floor_name,

'' AS room_name,

SUM(CO2_savings) As total_savings

From #CO2temp

group by subpolicy_name

UNION ALL

/** Getting Total Savings per Building*/

Select 'Building' AS Sector,

'' AS subpolicy_name,

building_name,

'' AS floor_name,

'' AS room_name,

SUM(CO2_savings) As total_savings

From #CO2temp

group by building_name

UNION ALL

/** Getting Total Savings per Floor*/

Select 'Floor' AS Sector,

'' AS subpolicy_name,

'' AS building_name,

floor_name,

'' AS room_name,

SUM(CO2_savings) As total_savings

From #CO2temp

group by floor_name

UNION ALL

/** Getting Total Savings per Room*/

Select 'Room' AS Sector,

'' AS subpolicy_name,

'' AS building_name,

'' AS floor_name,

room_name,

SUM(CO2_savings) As total_savings

From #CO2temp

group by room_name

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nialltuohy Right there with Babe Points: 766 More actions · Answer 4

Thanks for the reply, this very nearly works thanks very much appreciate it, the problem with the reports i have to use unfortunately is that they wont process multiple results sets. They are mainly bar graphs so you have to assign a column to the X and Y axis. The code you gave me works very well so for example for the 1st result if i assign subpolicy_name to the X axis and the total_savings column to the Y axis i get all the totals from the total_savings column displaying in the chart when in fact i only need the first 5 for the subpolicy_name.

Maybe Im trying to do the impossible Im not sure 🙁

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

nialltuohy (1/12/2010)
Thanks for the reply, this very nearly works thanks very much appreciate it, the problem with the reports i have to use unfortunately is that they wont process multiple results sets. They are mainly bar graphs so you have to assign a column to the X and Y axis. The code you gave me works very well so for example for the 1st result if i assign subpolicy_name to the X axis and the total_savings column to the Y axis i get all the totals from the total_savings column displaying in the chart when in fact i only need the first 5 for the subpolicy_name.
Maybe Im trying to do the impossible Im not sure 🙁

Niall, the code I posted generates 1 result set...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nialltuohy Right there with Babe Points: 766 More actions · Answer 6

Chris,

Thanks, i know it does but i have one small problem, i also need the savings to be split into 4 columns, for example a policy savings column, building_savings, floor_savings, and room_savings, Similar to the way there is 4 separate columns for subpolicy_name, building_name, floor_name and room_name. I have to do it this way because when i assign the total_savings column to the Y axis in my chart it shows all the results in that column when i only need the results for what ever category im showing, i.e. policy or building etc

Apologies for all the questions but this is a bit beyond my SQl knowledge 🙁 Really appreciate the help

homebrew01 SSC Guru Points: 55566 More actions · Answer 7

homebrew01

SSC Guru

Points: 55566

January 12, 2010 at 9:49 am

#1102551

Edit: error in my post.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

nialltuohy (1/12/2010)
Chris,
Thanks, i know it does but i have one small problem, i also need the savings to be split into 4 columns, for example a policy savings column, building_savings, floor_savings, and room_savings, Similar to the way there is 4 separate columns for subpolicy_name, building_name, floor_name and room_name. I have to do it this way because when i assign the total_savings column to the Y axis in my chart it shows all the results in that column when i only need the results for what ever category im showing, i.e. policy or building etc
Apologies for all the questions but this is a bit beyond my SQl knowledge 🙁 Really appreciate the help

That's why aI created an extra column, sector.

May I suggest that you show how you would like your data to look?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nialltuohy Right there with Babe Points: 766 More actions · Answer 9

Well the only solution i can think of is having all separate columns for everything. When the chart runs it needs to grab data from a column, so lets say for its X axis we assign subpolicy_name and for its Y axis we assign total_savings as it currently is, it will then display every value in the total_savings column. If i can use separate columns i think this should work, for example by assigning X axis as subpolicy_name and Y axis as a separate column policy_savings then it should work, i only want to display the 5 or 6 records which would be the policy savings.

So basically similar to the way there are separate columns for policy, building, floor, room instead of having the savings in one total savings column if i could break down into 4 columns one for each the

subpolicy into a policy_savings column

building into a building_savings column

floor into a floor_savings column

room into a room_savings column

Sorry i couldn't post my desired results but wouldn't display properly.

Hope that makes sense maybe im trying to do the impossible i don't know 🙁 Thanks again