Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Roll Up Function Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 4:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:08 AM
Points: 107, Visits: 177
HI Guys,


CREATE TABLE My_Table ( Work_Id VARCHAR(100),
Project_name VARCHAR(100),country VARCHAR(100),region VARCHAR(200),
Current_Actual_Saving FLOAT,Current_Forecast FLOAT,Current_Metric FLOAT,
Prior_Actual_Saving FLOAT,Prior_Forecast FLOAT,Prior_Metric FLOAT,Prior_2_Actual_Forecast FLOAT,
Prior_2_Forecst FLOAT,Prior_2_Metric FLOAT)

GO

Insert into My_table Values ('1800m480000icbch6ovg000000', 'BT - Increase Damage Recovery' ,'Budget Truck', 'Other Projects Which Help Truck', NULL ,23124 ,NULL, NULL, 21798, NULL ,3387, 22795 ,15)
Insert into My_table Values ('1800m200000hlelo5o0g000004' ,'CLE-Budget QTA at Airport Stall and RCRP(Replication)' ,'Domestic', 'NEA', NULL ,NULL ,NULL ,NULL, NULL ,NULL ,NULL ,NULL ,NULL)
Insert into My_table Values ('1800m200000htmsmn01g000000', 'CVG - ABG - Reduce Time to Maintenance', 'Domestic', 'NEA', NULL, 4920, NULL, NULL, 4463, NULL, 6514.5,3758 ,173)
Insert into My_table Values ('1800m480000iclij3eg0000000', 'IAD - Avis - Right Car One Way (Replication)', 'INTERNATIONAL' ,'NEA' ,NULL, 0 ,NULL ,NULL, 0 ,NULL ,NULL ,0 ,NULL)
Insert into My_table Values ('1800m480000iclirae0g000000', 'IAD - Budget - Right Car One Way (Replication)', 'Domestic' ,'NEA', NULL ,0 ,NULL ,NULL ,0 ,NULL ,NULL, 0, NULL)
Insert into My_table Values ('1800m480000ifc87sk3g000000', 'Massachusetts Excise Tax', 'Domestic' ,'NEA', NULL ,14110, NULL, NULL, 14110, NULL ,12563 ,14110, 89)
Insert into My_table Values ('1800m480000iheoeabng000000', 'EWR Avis Yield Mgmt (Replication)' ,'Domestic', 'NEA', NULL ,0 ,NULL ,NULL, 0 ,NULL ,NULL, 0 ,NULL)
Insert into My_table Values ('1800m480000ihih770sg000000', 'PVD- Avis- Yield Management (Replication)', 'Domestic' ,'NEA', NULL, 5830 ,NULL ,NULL ,4430, NULL, -1 ,2775, 0)
Insert into My_table Values ('1800m480000ijqq69rt0000000', 'ERO-Impact Damage' ,'Domestic' ,'NEA', NULL, 37840, NULL, NULL, 32051 ,NULL, 88872, 32132, 277)
Insert into My_table Values ('1800m480000ijqvqa3g0000000', 'Inbound Rate Validation Project', 'Domestic' ,'NEA' ,NULL, 213065, NULL, NULL ,128184 ,NULL, 69496.92, 68539 ,101)
Insert into My_table Values ('1800m480000ik7nknevg000000', 'BWI - Avis - Yield Management (Replication)', 'Domestic' ,'NEA' ,NULL ,1 ,NULL, NULL ,1 ,NULL ,-1 ,1, -100)
Insert into My_table Values ('1800m480000ik8qohvv0000000', 'New England - ABG - Make/Model Fleet Mix', 'Domestic', 'NEA' ,NULL, 23965 ,NULL, NULL, 23494, NULL, -1, 18359, 0)
Insert into My_table Values ('1800m480000im353q7o0000000', 'CLE - Avis - Yield Management (Replication)', 'Domestic' ,'NEA' ,NULL ,1 ,NULL, NULL, 1 ,NULL,-1 ,1 ,-100)
Insert into My_table Values ('1800m480000imp19242g000000', 'PHL - ABG - Fuel Accountability (Replication)', 'Domestic' ,'NEA' ,NULL ,912, NULL, NULL ,3898, NULL ,68992 ,1 ,6899200)
Insert into My_table Values ('1800m480000in20f5n9g000000', 'LM - 6110 - Michigan - Local Market Ancillary Sales', 'Domestic' ,'NEA', NULL, 6506.986794, NULL, NULL, 4691.353252 ,NULL ,9214, 3832.792079 ,240)
Insert into My_table Values ('1800m480000ik8t7pqs0000000', 'MDW - ABG - Optimum Volume Segmentation' ,'Domestic', 'Northern Central Area' ,NULL ,18603 ,NULL, NULL ,16350, NULL ,-2 ,33171 ,0)
Insert into My_table Values ('1800m480000im880va80000000', 'STL - ABG - Walk Up Enhancement (Replication)' ,'Domestic' ,'Northern Central Area' ,NULL, 2366 ,NULL, NULL, 4186,NULL ,821, 1600, 51)
Insert into My_table Values ('1800m480000in300arog000004', 'IND - ABG - Fuel Accountability (Replication)' ,'Domestic' ,'Northern Central Area', NULL ,3596 ,NULL ,NULL ,3316, NULL ,9930, 3503, 283)
Insert into My_table Values ('1800m200000i4ckgbmf0000000', 'AB Impound Process', 'Domestic', 'Other projects which help Domestic' ,NULL ,103626 ,NULL ,NULL, 100518 ,NULL, 86884, 97141, 89)
Insert into My_table Values ('1800m200000i8aqi6o8g000000', 'Liability/PIP Subrogation' ,'Domestic' ,'Other projects which help Domestic' ,NULL ,15000 ,NULL, NULL, 15000, NULL ,21686 ,15000, 145)

GO


I want to Retrieve the all records with summing float column based on country & Region

For Exampl

City Country State Population
Bangalore India Karnataka 1.20
Mysore India XYZ 20.2
Bangalore India XMV 2.02
Bangalore India NUll 2.20

Post #1341117
Posted Tuesday, August 7, 2012 4:25 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:48 AM
Points: 689, Visits: 2,785
I may be misunderstanding what you are looking for:

If you would like to elaborate?

SELECT  
country
,region
,SUM(Current_Actual_Saving) AS 'Current_Actual_Saving'
,SUM(Current_Forecast) AS 'Current_Forecast'
,SUM(Current_Metric) AS 'Current_Metric'
,SUM(Prior_Actual_Saving) AS 'Prior_Actual_Saving'
,SUM(Prior_Forecast) AS 'Prior_Forecast'
,SUM(Prior_Metric) AS 'Prior_Metric'
,SUM(Prior_2_Actual_Forecast) AS 'Prior_2_Actual_Forecast'
,SUM(Prior_2_Forecst) AS 'Prior_2_Forecst'
,SUM(Prior_2_Metric) AS 'Prior_2_Metric'

FROM
My_Table

GROUP BY
country
,region

--DROP TABLE
--My_Table



==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1341131
Posted Tuesday, August 7, 2012 4:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
You can roll up selected values and add them to each record like this:

SELECT Country,Region, Current_Forecast,
SUM(Current_Forecast) OVER (PARTITION BY Country, Region) as Sum_Current_Forecast
FROM My_Table

Post #1341137
Posted Tuesday, August 7, 2012 4:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:08 AM
Points: 107, Visits: 177
Thanks for the Reply.,

But i want to Show Workid,Project_name as well in the query
Post #1341139
Posted Tuesday, August 7, 2012 4:39 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:48 AM
Points: 689, Visits: 2,785
Which Query?

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1341140
Posted Tuesday, August 7, 2012 4:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
If you mean you want to keep the original 20 records with extra summed columns, you can do this:

SELECT Work_Id, 
Project_name,
country,
region,
Current_Actual_Saving,
Current_Forecast,
Current_Metric,
Prior_Actual_Saving,
Prior_Forecast,
Prior_Metric,
Prior_2_Actual_Forecast,
Prior_2_Forecst,
Prior_2_Metric,
SUM(Current_Forecast) OVER (PARTITION BY Country, Region) as Sum_Current_Forecast
FROM My_Table

Remove any columns you don't want, & add any SUM()s you do want.
Post #1341142
Posted Tuesday, August 7, 2012 4:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:08 AM
Points: 107, Visits: 177
Sum should be in rows instead as mentioned in column wise

For every Country & region

I want to show in the Rows as i should in the example

Post #1341144
Posted Tuesday, August 7, 2012 5:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
Can you give us an example that relates to the sample data you've posted?
Post #1341151
Posted Tuesday, August 7, 2012 5:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:08 AM
Points: 107, Visits: 177
'1800m480000ik8t7pqs0000000',	'MDW - ABG - Optimum Volume Segmentation'	,'Domestic',	'Northern Central Area'	,NULL	,18603	,NULL,	NULL	,16350,	NULL	,-2	,33171	,0
'1800m480000im880va80000000', 'STL - ABG - Walk Up Enhancement (Replication)' ,'Domestic' ,'Northern Central Area' ,NULL, 2366 ,NULL, NULL, 4186,NULL ,821, 1600, 51
'1800m480000in300arog000004', 'IND - ABG - Fuel Accountability (Replication)' ,'Domestic' ,'Northern Central Area', NULL ,3596 ,NULL ,NULL ,3316, NULL ,9930, 3503, 283

the Next row should be lyk given below: Should sumbased on region here So WorkId,Projectname will come Blank

Dmestic Northern Central Area Null ,24565,---This current Forecast
Post #1341160
Posted Tuesday, August 7, 2012 5:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
This will create a table with total lines. It's not very set-based though, & you end up with the TotalLine column which you need to order it....

WITH Totals AS
(
SELECT
2 as TotalLine,
CAST('' as Varchar(100)) as Work_Id,
CAST('' as Varchar(100)) as Project_name,
Country,
Region,
SUM(Current_Actual_Saving) AS Current_Actual_Saving,
SUM(Current_Forecast) AS Current_Forecast,
SUM(Current_Metric) AS Current_Metric,
SUM(Prior_Actual_Saving) AS Prior_Actual_Saving,
SUM(Prior_Forecast) AS Prior_Forecast,
SUM(Prior_Metric) AS Prior_Metric,
SUM(Prior_2_Actual_Forecast) AS Prior_2_Actual_Forecast,
SUM(Prior_2_Forecst) AS Prior_2_Forecst,
SUM(Prior_2_Metric) AS Prior_2_Metric
FROM My_Table
GROUP BY Country, Region
)
SELECT
1 as TotalLine,
Work_Id,
Project_name,
country,
region,
Current_Actual_Saving,
Current_Forecast,
Current_Metric,
Prior_Actual_Saving,
Prior_Forecast,
Prior_Metric,
Prior_2_Actual_Forecast,
Prior_2_Forecst,
Prior_2_Metric
FROM My_Table
UNION ALL
SELECT *
FROM Totals
ORDER BY Country, Region, TotalLine, Work_Id, Project_Name;

Post #1341172
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse