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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Sum

Author  Topic 

mrivero1961
Starting Member

7 Posts

Posted - 2013-05-25 : 13:31:25
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.[/code]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-25 : 20:15:26
[CODE]


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;


[/CODE]
Go to Top of Page

mrivero1961
Starting Member

7 Posts

Posted - 2013-05-26 : 17:20:28
quote:
Originally posted by MuMu88

[CODE]


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;


[/CODE]



thanks so much
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 01:14:03
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
   

- Advertisement -