August 7, 2012 at 4:01 am
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
August 7, 2012 at 4:25 am
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
August 7, 2012 at 4:37 am
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
August 7, 2012 at 4:37 am
Thanks for the Reply.,
But i want to Show Workid,Project_name as well in the query
August 7, 2012 at 4:39 am
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
August 7, 2012 at 4:42 am
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.
August 7, 2012 at 4:47 am
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
August 7, 2012 at 5:04 am
Can you give us an example that relates to the sample data you've posted?
August 7, 2012 at 5:28 am
'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
August 7, 2012 at 5:48 am
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;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply