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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrivero1961
Starting Member

7 Posts

Posted - 05/25/2013 :  13:31:25  Show Profile  Reply with Quote
Hi all, hope in your help.

This is my table:

+----+--------+--------+
| id | field1 | field2 |
+----+--------+--------+
|  1 | A1     | 7      |
|  2 | B1     | 9      |
|  3 | C1     | 0      |
|  4 | D1     | 3      |
|  5 | A2     | 5      |
|  6 | B2     | 6      |
|  7 | C2     | 7      |
|  8 | D2     | 8      |
+----+--------+--------+


I need this output:

+--------+--------------------+
| field1 | field2             |
+--------+--------------------+
| A2     | 19.230769230769200 |
+--------+--------------------+
| B2     | 23,076923076923100 |
+--------+--------------------+
| C2     | 26,923076923076900 |
+--------+--------------------+
| D2     | 30,769230769230800 |
+--------+--------------------+

and tried this query

where calculate the value of single field1 (5,6,7,8) divided by the sum of field2 equal to A1, B2, C2 and D2 (26) :

A = 5/26 * 100 = 19
B = 6/26 * 100 = 23
C = 7/26 * 100 = 26
D = 8/26 * 100 = 30

SELECT
	field1,
	field2/Sum(field2)*100 as field2
FROM
	`tbl_t`
WHERE
	1
AND field1 IN ('A2', 'B2', 'C2', 'D2');

+--------+--------------------+
| field1 | field2             |
+--------+--------------------+
| A2     | 19.230769230769234 |
+--------+--------------------+

But the ouput is not what I want, can you help me?

Thank you
Any help would be greatly appreciated.

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/25/2013 :  20:15:26  Show Profile  Reply with Quote



DECLARE @Tbl_t TABLE(ID INT, field1 NVARCHAR(2), field2 INT)
INSERT INTO @Tbl_t 
SELECT 1, 'A1', 7 union all
SELECT 2, 'B1', 9 union all
SELECT 3, 'C1', 0 union all
SELECT 4, 'D1', 3 union all
SELECT 5, 'A2', 5 union all
SELECT 6, 'B2', 6 union all
SELECT 7, 'C2', 7 union all
SELECT 8, 'D2', 8;



WITH CTE AS
(SELECT CASE WHEN (field1 IN ('A2', 'B2', 'C2', 'D2')) THEN 1 ELSE 2 END as Grp, * from @tbl_t)
SELECT field1, CAST(CAST(field2*100 as DECIMAL(20,15))/(SELECT SUM(field2) from CTE where Grp = 1 Group by Grp) as DECIMAL(20,15)) as Average 
	FROM CTE WHERE Grp = 1;



Go to Top of Page

mrivero1961
Starting Member

7 Posts

Posted - 05/26/2013 :  17:20:28  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88




DECLARE @Tbl_t TABLE(ID INT, field1 NVARCHAR(2), field2 INT)
INSERT INTO @Tbl_t 
SELECT 1, 'A1', 7 union all
SELECT 2, 'B1', 9 union all
SELECT 3, 'C1', 0 union all
SELECT 4, 'D1', 3 union all
SELECT 5, 'A2', 5 union all
SELECT 6, 'B2', 6 union all
SELECT 7, 'C2', 7 union all
SELECT 8, 'D2', 8;



WITH CTE AS
(SELECT CASE WHEN (field1 IN ('A2', 'B2', 'C2', 'D2')) THEN 1 ELSE 2 END as Grp, * from @tbl_t)
SELECT field1, CAST(CAST(field2*100 as DECIMAL(20,15))/(SELECT SUM(field2) from CTE where Grp = 1 Group by Grp) as DECIMAL(20,15)) as Average 
	FROM CTE WHERE Grp = 1;






thanks so much
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  01:14:03  Show Profile  Reply with Quote
this is the explanation

http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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