SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [SQL Server 2005] Problem SUM in subquery

    Hi all, your help please.

    Why this query in SQL Server 2005 I have this output (column PERC is 0 values):
    Code:
    MAT	A	B	C	D	PERC
    1D	54050	1195	11468	72659	0
    1F	87761	1486	14899	109090	0
    1G	54992	1031	10390	73672	0
    1H	77815	374	10553	93879	0
    1I	71087	1921	13430	91839	0
    1M	74800	3338	9659	99771	0
    1O	92413	4476	11321	124779	0
    1P	76739	1027	11222	100764	0
    1Q	47642	2379	7135	66116	0
    1R	139038	3888	19909	185092	0
    1S	30214	363	4380	39269	0
    Tot	806551	21478	124366	1056930	0
    instead of this correct output:
    Code:
    MAT	A	B	C	D	PERC
    1D	54050	1195	11468	72659	0,918165678
    1F	87761	1486	14899	109090	0,954679622
    1G	54992	1031	10390	73672	0,901468672
    1H	77815	374	10553	93879	0,945280627
    1I	71087	1921	13430	91839	0,941190562
    1M	74800	3338	9659	99771	0,879985166
    1O	92413	4476	11321	124779	0,867213233
    1P	76739	1027	11222	100764	0,883132865
    1Q	47642	2379	7135	66116	0,86448061
    1R	139038	3888	19909	185092	0,879751691
    1S	30214	363	4380	39269	0,890193282
    Tot	806551	21478	124366	1056930	0,901095626
    Can you help me?
    Thanks in advance, your help is very appreciated.
    Code:
    SELECT
    	COALESCE ([MAT], 'Tot') AS [MAT],
    	[A],
    	[B],
    	[C],
    	[D],
    	(
    		[A] + [B] + [C]
    	) / ([D]) AS [PERC]
    FROM
    	(
    		SELECT
    			LEFT (TZZ, 2) AS MAT,
    			COUNT (*) AS [D],
    			SUM (
    				CASE
    				WHEN (
    					[RM] = '1'
    					OR [RM] = '2'
    					OR [RM] = '3'
    					OR [RM] = '4'
    				)
    				AND [DV] IS NOT NULL THEN
    					1
    				ELSE
    					0
    				END
    			) AS [A],
    			SUM (
    				CASE
    				WHEN (
    					[RM] = '5'
    					AND [DV] IS NOT NULL
    				) THEN
    					1
    				ELSE
    					0
    				END
    			) AS [B],
    			SUM (
    				CASE
    				WHEN (
    					(
    						[RM] = '6'
    						OR [RM] = '7'
    					)
    					AND [DV] IS NOT NULL
    				) THEN
    					1
    				ELSE
    					0
    				END
    			) AS [C]
    		FROM
    			dbo_42
    		WHERE
    			1 = 1
    		AND [TZZ] NOT LIKE 'LG%'
    		GROUP BY
    			LEFT (TZZ, 2) WITH ROLLUP
    	) AS SubQs;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,234
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    Your columns A-D are all INT, so you need to CAST at least one of them to DECIMAL in your equation for PERC, like so,

    Code:
    (
    		[A] + [B] + [C]
    	) / CAST([D] AS DECIMAL(10,8)) AS [PERC]

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Your columns A-D are all INT, so you need to CAST at least one of them to DECIMAL in your equation for PERC, like so,

    Code:
    (
    		[A] + [B] + [C]
    	) / CAST([D] AS DECIMAL(10,8)) AS [PERC]
    thanks for help, but I have this new error in the query:
    Code:
    [Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type numeric.
    If tried this:
    Code:
    CAST([D] AS DECIMAL(10,2)) AS [PERC]
    I have this output: .91816567803

    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,234
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    I ran the following:
    Code:
    CREATE TABLE dbo_42
    (
    	[TZZ] VARCHAR(3),
    	[RM] VARCHAR(1),
    	[DV] VARCHAR(1) NULL
    )
    
    INSERT INTO dbo_42 VALUES ('1D', '3', '3')
    INSERT INTO dbo_42 VALUES ('1D', '4', NULL)
    INSERT INTO dbo_42 VALUES ('1D', '6', '3')
    INSERT INTO dbo_42 VALUES ('1D', '7', '3')
    INSERT INTO dbo_42 VALUES ('1D', '7', '3')
    INSERT INTO dbo_42 VALUES ('1D', '6', '3')
    INSERT INTO dbo_42 VALUES ('1D', '4', '3')
    INSERT INTO dbo_42 VALUES ('1D', '5', '3')
    INSERT INTO dbo_42 VALUES ('1D', '1', '3')
    
    SELECT
    	COALESCE ([MAT], 'Tot') AS [MAT],
    	[A],
    	[B],
    	[C],
    	[D],
    	([A] + [B] + [C]) / CAST([D] AS DECIMAL(10,2)) AS [PERC]
    FROM
    	(
    		SELECT
    			LEFT (TZZ, 2) AS MAT,
    			COUNT (*) AS [D],
    			SUM (
    				CASE
    				WHEN (
    					[RM] = '1'
    					OR [RM] = '2'
    					OR [RM] = '3'
    					OR [RM] = '4'
    				)
    				AND [DV] IS NOT NULL THEN
    					1
    				ELSE
    					0
    				END
    			) AS [A],
    			SUM (
    				CASE
    				WHEN (
    					[RM] = '5'
    					AND [DV] IS NOT NULL
    				) THEN
    					1
    				ELSE
    					0
    				END
    			) AS [B],
    			SUM (
    				CASE
    				WHEN (
    					(
    						[RM] = '6'
    						OR [RM] = '7'
    					)
    					AND [DV] IS NOT NULL
    				) THEN
    					1
    				ELSE
    					0
    				END
    			) AS [C]
    		FROM
    			dbo_42
    		WHERE
    			1 = 1
    		AND [TZZ] NOT LIKE 'LG%'
    		GROUP BY
    			LEFT (TZZ, 2) WITH ROLLUP
    	) AS SubQs;
    	
    DROP TABLE dbo_42
    And I get the following output:
    Code:
    MAT  A           B           C           D           PERC
    ---- ----------- ----------- ----------- ----------- ----------------------
    1D   3           1           4           9           0.88888888888
    Tot  3           1           4           9           0.88888888888
    I'm assuming you are in a culture that uses commas for decimal points instead of a period. That is likely a setting on the table/database itself, via localization.

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,234
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    Bugger, I re-read your error, so the arithmetic overflow would be solved by tweaking the decimal parameters. I won't be able to necessarily tweak those for you, but I can say 10,2 is a typical value used.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    I have this output: .91816567803
    is that not the right answer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    is that not the right answer?
    Why .91816567803 and not 0.91816567803 ?
    thank you
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,234
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Why .91816567803 and not 0.91816567803 ?
    thank you
    Okay, for the longest time I wasn't getting the same result as you, I always got the leading zero, so I thought... what could he be doing that I am doing differently? Then it hit me, you might be using Query Analyzer to run your queries.

    So I took my SQL code and ran it in Query Analyzer, and ta da! No leading 0's. When I run it in SSMS, it has the leading 0's.

    I did some basic searching only found that Query Analyzer removes the leading 0's for all numeric types, but SSMS will only remove them for Floats and Ints. No explanation as to why, or how to change the behavior.

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Okay, many thanks for explanation!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •