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 = 19B = 6/26 * 100 = 23C = 7/26 * 100 = 26D = 8/26 * 100 = 30SELECT field1, field2/Sum(field2)*100 as field2FROM `tbl_t`WHERE 1AND field1 IN ('A2', 'B2', 'C2', 'D2');+--------+--------------------+| field1 | field2 |+--------+--------------------+| A2 | 19.230769230769234 |+--------+--------------------+ But the ouput is not what I want, can you help me?Thank youAny 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 allSELECT 2, 'B1', 9 union allSELECT 3, 'C1', 0 union allSELECT 4, 'D1', 3 union allSELECT 5, 'A2', 5 union allSELECT 6, 'B2', 6 union allSELECT 7, 'C2', 7 union allSELECT 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] |
|
|
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 allSELECT 2, 'B1', 9 union allSELECT 3, 'C1', 0 union allSELECT 4, 'D1', 3 union allSELECT 5, 'A2', 5 union allSELECT 6, 'B2', 6 union allSELECT 7, 'C2', 7 union allSELECT 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|