Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Sum Expand / Collapse
Author
Message
Posted Saturday, May 25, 2013 11:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:25 AM
Points: 14, Visits: 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.
Post #1456801
Posted Sunday, May 26, 2013 2:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 1,054, Visits: 3,122
Hi

You could try the following, I wouldn't expect the performance to be the best because of the partition on the RIGHT function. It may be worthwhile making the making a grouping column in your base table

with sampleData as (
select *
from (values
(1,'A1',7)
,(2,'B1',9)
,(3,'C1',0)
,(4,'D1',3)
,(5,'A2',5)
,(6,'B2',6)
,(7,'C2',7)
,(8,'D2',8)
) as sd(id, f1, f2)
)
select f1, (cast(f2 as float) / sum(f2) over (partition by right(a.f1,1))) * 100.0 as f2
from sampleData a
where right(a.f1, 1) = 2

Post #1456922
Posted Sunday, May 26, 2013 3:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:25 AM
Points: 14, Visits: 25
thanks so much
Post #1456927
Posted Tuesday, May 28, 2013 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 7:41 AM
Points: 8, Visits: 3
Here is my take on it, I see 2 ways of doing it, both equally efficient just depends on what you want to do

declare @f2 as float;
select @f2 = cast(SUM(field2) as float)
from test
where RIGHT(field1,1)='2';
select field1, field2/@f2*100.0 as field2
from test
where RIGHT(field1,1)='2';

As you can see above, I start by declaring a variable, and then proceed to define it using an aggregate function (sum), and use the same where condition as in the final select, this way I ensure that I have selected the correct fields for the aggregate function to perform the final calculation.

select field1, CAST(field2 as float)/SUM(field2) over (partition by right(field1,1))
* 100.0 as field2
from test
where RIGHT(field1,1)='2';

Now in this example we use the over cluase to avoid having to group any columns not in aggregate functions, and therefore produce a simple one liner.

This in turn produces the results that you are looking for in a simple fashion

field1	field2
A2 19.2307692307692
B2 23.0769230769231
C2 26.9230769230769
D2 30.7692307692308


Jaime Diaz
Lyntek, Inc
Post #1457311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse