getting duplicates for every row

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

getting duplicates for every row

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-25 : 15:11:32

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-25 : 15:16:35

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

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-25 : 15:51:16

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)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-25 : 17:30:16

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

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-28 : 16:54:48

Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources