SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 getting duplicates for every row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anjali5
Posting Yak Master

110 Posts

Posted - 04/25/2014 :  15:11:32  Show Profile  Reply with Quote
Hi All,

I have this data and I want to calculate the total based on fiscalyear_title



pk	fiscalyear_title	fiscal_data1	fiscal_data2	fiscal_data3	fiscal_total
7	US.TEST	                12.234	        12.456	         89.786	        114.476
7	US.TEST1	        45.987	        45.678	         34.987	        126.652
7	US.TEST3	        67.456	        34.234	         98.567	        200.257
7	GE.TEST1	        678.987	        34.456	         34.567	        748.010
7	GE.TEST2	        98.567	        66.789	         55.786	        221.142
7	NL.TEST1	        67.567	        34.567	         98.789	        200.923
7	NL.TEST2	        33.567	        34.789	         33.567	        101.923
7	NL.TEST3	        99.678	        44.678	         34.567	        178.923
7* Grand Total Costs	       1104.043	       307.647	         480.616	1892.306



I want something like this



I want something like this

pk	fiscalyear_title	fiscal_data1	fiscal_data2	fiscal_data3	fiscal_total
7	US.TEST	                12.234	        12.456     89.786              114.476
7	US.TEST1	        45.987	        45.678	         34.987	        126.652
7	US.TEST3	        67.456	        34.234	         98.567	        200.257
        US. Total Costs         125.677         92.368           223.34         441.385

7	GE.TEST1	        678.987	        34.456	         34.567	        748.010
7	GE.TEST2	        98.567	        66.789	         55.786	        221.142
        GE. Total Costs     777.554         101.245               90.353            969.152

7	NL.TEST1	        67.567	        34.567	         98.789	        200.923
7	NL.TEST2	        33.567	        34.789	         33.567	        101.923
7	NL.TEST3	        99.678	        44.678	         34.567	        178.923
        NL.Total Cost           200.812         114.034          167.923	481.769


7* Grand Total Costs	       1104.043	       307.647	         480.616	1892.306








I got this query that does that. All I want is to add pk in this query and join it with another table based on pK.


Select Coalesce(FISCALYEAR_TITLE, leftTwo +' Total Costs',' Grand Total Costs') as FISCALYEAR_TITLE , fiscal_data1, fiscal_data2, fiscal_data3, fiscal_total
from (
SELECT LeftTwo, FISCALYEAR_TITLE,SUM(FISCAL_DATA1) as fiscal_data1, SUM(FISCAL_DATA2) fiscal_data2, SUM(FISCAL_DATA3) fiscal_data3, SUM(FISCAL_TOTAL) fiscal_total
FROM ( select left(FISCALYEAR_TITLE,2) LeftTwo, FISCALYEAR_TITLE , FISCAL_DATA1, FISCAL_DATA2, FISCAL_DATA3, FISCAL_TOTAL
FROM mytable) t1

GROUP BY LeftTwo,FISCALYEAR_TITLE with rollup

)
t2

another table is like this




pk      fiscal_year1          fiscal_year2   fiscal_year3
3	2015	              2016	      2017
4	2015	              2016	      2016
5	2014	              2015	      2015
6	2014	              2015	      2015
7	2015	              2016	      2016
8	2015	              2016	      2016





so I want to do something like this

Select Coalesce(FISCALYEAR_TITLE, leftTwo +' Total Costs',' Grand Total Costs') as FISCALYEAR_TITLE , fiscal_data1, fiscal_data2, fiscal_data3, fiscal_total , pk
from (
SELECT LeftTwo, FISCALYEAR_TITLE,SUM(FISCAL_DATA1) as fiscal_data1, SUM(FISCAL_DATA2) fiscal_data2, SUM(FISCAL_DATA3) fiscal_data3, SUM(FISCAL_TOTAL) fiscal_total, pk
FROM ( select left(FISCALYEAR_TITLE,2) LeftTwo, FISCALYEAR_TITLE , FISCAL_DATA1, FISCAL_DATA2, FISCAL_DATA3, FISCAL_TOTAL , pk FROM MOU_FINANCIAL_CONTRIBUTION) t1
GROUP BY LeftTwo,FISCALYEAR_TITLE, pk with rollup

)
t2 INNER JOIN myTable2
ON t2.pk = myTable2.pk

I am getting duplicates for every row. Is it possible to eliminate this duplicates or does the query needs to be written differently.

any help will be appreciated.

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/25/2014 :  15:16:35  Show Profile  Reply with Quote
If you post some sample data in consumable format we can help you better.
Go to Top of Page

anjali5
Posting Yak Master

110 Posts

Posted - 04/25/2014 :  15:51:16  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

If you post some sample data in consumable format we can help you better.





CREATE TABLE #Tab (pk VARCHAR(20),fiscalyear_title VARCHAR(100),fiscal_data1 FLOAT,fiscal_data2 FLOAT,fiscal_data3 FLOAT,fiscal_total FLOAT)
INSERT INTO #tab VALUES 
('7','US.TEST1',12.234,12.456,89.786,114.476)
,('7','US.TEST1', 45.987,45.678,34.987,126.652)
,('7','US.TES3', 67.456,34.234,98.567,200.257)
,('7','GE.TEST1', 678.987,34.456,34.567,748.010)
,('7','GE.TEST2', 98.567,66.789,55.786,221.142)
,('7','NL.TEST1',67.567,34.567,98.789,200.923)
,('7','NL.TEST2', 33.567,34.789,33.567,101.923)
,('7','NL.TEST3', 99.678,44.678,34.567,178.923)
,('7 *','Grand Total Costs',1104.043,307.647,480.616,1892.306)



Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/25/2014 :  17:30:16  Show Profile  Reply with Quote
quote:
Originally posted by anjali5

Hi All,

I have this data and I want to calculate the total based on fiscalyear_title



pk	fiscalyear_title	fiscal_data1	fiscal_data2	fiscal_data3	fiscal_total
7	US.TEST	                12.234	        12.456	         89.786	        114.476
7	US.TEST1	        45.987	        45.678	         34.987	        126.652
7	US.TEST3	        67.456	        34.234	         98.567	        200.257
7	GE.TEST1	        678.987	        34.456	         34.567	        748.010
7	GE.TEST2	        98.567	        66.789	         55.786	        221.142
7	NL.TEST1	        67.567	        34.567	         98.789	        200.923
7	NL.TEST2	        33.567	        34.789	         33.567	        101.923
7	NL.TEST3	        99.678	        44.678	         34.567	        178.923
7* Grand Total Costs	       1104.043	       307.647	         480.616	1892.306



I want something like this



I want something like this

pk	fiscalyear_title	fiscal_data1	fiscal_data2	fiscal_data3	fiscal_total
7	US.TEST	                12.234	        12.456     89.786              114.476
7	US.TEST1	        45.987	        45.678	         34.987	        126.652
7	US.TEST3	        67.456	        34.234	         98.567	        200.257
        US. Total Costs         125.677         92.368           223.34         441.385

7	GE.TEST1	        678.987	        34.456	         34.567	        748.010
7	GE.TEST2	        98.567	        66.789	         55.786	        221.142
        GE. Total Costs     777.554         101.245               90.353            969.152

7	NL.TEST1	        67.567	        34.567	         98.789	        200.923
7	NL.TEST2	        33.567	        34.789	         33.567	        101.923
7	NL.TEST3	        99.678	        44.678	         34.567	        178.923
        NL.Total Cost           200.812         114.034          167.923	481.769


7* Grand Total Costs	       1104.043	       307.647	         480.616	1892.306









Here is "a" way to do that. You might be able to use GROUPING SETS, but I'm not sure they'll get you the results you want:
SELECT 
	pk
	,fiscalyear_title
	,fiscal_data1
	,fiscal_data2
	,fiscal_data3
	,fiscal_total
FROM
	(
		SELECT *, 1 AS GroupRank
		FROM #Tab

		UNION ALL

		SELECT 
			'' AS pk
			,LEFT(fiscalyear_title, 3) + 'Total Costs'
			,SUM(fiscal_data1)
			,SUM(fiscal_data2)
			,SUM(fiscal_data3)
			,SUM(fiscal_total)
			,2 AS GroupRank
		FROM 
			#Tab
		WHERE
			fiscalyear_title <> 'Grand Total Costs'
		GROUP BY
			LEFT(fiscalyear_title, 3)
	) AS Temp
ORDER BY
	CASE WHEN fiscalyear_title <> 'Grand Total Costs' THEN 0 ELSE 1 END
	,LEFT(fiscalyear_title, 3)
	,GroupRank
Go to Top of Page

anjali5
Posting Yak Master

110 Posts

Posted - 04/28/2014 :  16:54:48  Show Profile  Reply with Quote
Thank you!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000