Problems with "Group By" statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problems with "Group By" statement

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-12-10 : 06:40:21

Im using Stored Procedure and MS chart controls for reporting purposes. I need to be able to group my results by policy, building, floor and room. The way my code is written at the moment i have multiple "group by" statements, they work individually but when using MS Chart Controls its not recognizing the 2nd group by statement, it always only recognizes the 1st, for example if i point to the first "group by" statement it works but then if i change it and try the 2nd one it does not work, says its not recognized, no matter what statement i put first it always works but wont move to the 2nd one.

Ive tried a multiple group by statement, this does work in the charts but its producing duplicate rows, you can see this in my code i've put it in red.

My code is below, Not sure why the multiple group by statement is not working, would appreciate if anyone could explain why, thanks I guess one option would be to write three more stored procedures and have one "group by" statement in each, would rather do it in one stored procedure but i guess this would work




ALTER PROCEDURE dbo.SP_KWH_REPORT

AS

/* Create temp table */
	CREATE TABLE #KWHtemp
	(
	pc_profile_id int,
	shutdown_Time datetime NULL, 
	hibernate_Time datetime NULL, 
	sleep_Time datetime NULL, 
	startup_Time datetime NULL,
	status varchar(50),
	subpolicy_name varchar(50),
	building_name varchar(50),
	floor_name varchar(50),
	room_name varchar(50),
	total_hrs_off int, 
	pc_kwh_rate float,
	KWH_savings float
	)
		
	/** Insert Values into Temp Table from View_Savings_Report*/
	
	insert into #KWHtemp (pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, pc_kwh_rate, subpolicy_name, building_name, floor_name, room_name)
	SELECT PC_PROFILE_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, PC_POWER_RATING, SUB_POLICY_NAME, BUILDING_NAME, FLOOR_NAME, ROOM_NAME
	FROM VIEW_KWH_REPORT 	

	/** Get total hours computer is off*/
	UPDATE #KWHtemp
	SET total_hrs_off = DATEDIFF(HOUR, 
	COALESCE(shutdown_Time,hibernate_Time,sleep_Time,startup_Time),
	COALESCE(startup_Time,sleep_Time,hibernate_Time,shutdown_Time))
	

	
	/** Calculate Total KWH Savings*/ 
	UPDATE #KWHtemp
	SET KWH_savings = (isnull(total_hrs_off, 0) * pc_kwh_rate)

	
     /**Select subpolicy_name, building_name, floor_name, room_name, SUM(KWH_savings) As savings
	From #KWHtemp
	group by subpolicy_name, building_name, floor_name, room_name
	order by subpolicy_name, building_name, floor_name,room_name;*/
 
	
	
	/** Getting Total Savings per Policy*/
	Select subpolicy_name, SUM(KWH_savings) As policy_savings
	From #KWHtemp
	group by subpolicy_name
 
	/** Getting Total Savings per Building*/
	Select building_name, SUM(KWH_savings) As building_savings
	From #KWHtemp
	group by building_name 
	
	/** Getting Total Savings per Floor*/
	Select floor_name, SUM(KWH_savings) As floor_savings
	From #KWHtemp
	group by floor_name
	
	/** Getting Total Savings per Room*/
	Select room_name, SUM(KWH_savings) As room_savings
	From #KWHtemp
	group by room_name

	RETURN

niall

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2009-12-11 : 02:29:44

Can you please tell me how you are calling the procedure in the MS Chart?

Regards,
Sathieshkumar. R

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-12-11 : 07:36:06

quote:
Originally posted by sathiesh2005

Can you please tell me how you are calling the procedure in the MS Chart?

Regards,
Sathieshkumar. R

Thanks for the post.I pretty much just a have a SQL Data source on my page and have my stored procedure selected from there, this is my code, ull see the Chart Control and then the Data Soucre. This is not a major problem but im new to using Stored procedures and eager to know how to do it because along with Grouping my results in a number of different groups, I also hope to filter my results in relation to the date, for example previous week, previous month report etc. so be nice to know how to do this properly. Thanks


 <asp:Chart ID="Chart1" runat="server" DataSourceID="SqlDataSource1"  
            Height="404px" Width="455px" Palette="None" PaletteCustomColors="Green">
            <Titles>
             <asp:Title ShadowColor="32, 0, 0, 0" Font="Trebuchet MS, 14.25pt, style=Bold" ShadowOffset="3"
                 Text="Savings Overview" Alignment="TopLeft" ForeColor="26, 59, 105">
                </asp:Title>
             </Titles>          
            <Series>
                <asp:Series Name="Series1" BorderColor="180, 26, 59, 105" XValueMember="subpolicy_name"
                            YValueMembers="total_savings">
                </asp:Series>
            </Series>
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1" BorderColor="64, 64, 64, 64" BorderDashStyle="Solid"
                            BackSecondaryColor="White" BackColor="64, 165, 191, 228" ShadowColor="Transparent"
                            BackGradientStyle="TopBottom">
                    <Area3DStyle Enable3D="True" LightStyle="Realistic" />
                </asp:ChartArea>
            </ChartAreas>
        </asp:Chart>

                <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:copitConnectionString %>" 
                    SelectCommand="SP_SAVINGS_REPORT" SelectCommandType="StoredProcedure"></asp:SqlDataSource>

niall

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources