Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

549 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
52326 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  
 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.05 seconds. Powered By: Snitz Forums 2000