Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Query Sum Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, May 25, 2013 11:32 AM
 Grasshopper 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 = 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.
Post #1456801
 Posted Sunday, May 26, 2013 2:38 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 HiYou 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 f2from sampleData awhere right(a.f1, 1) = 2`
Post #1456922
 Posted Sunday, May 26, 2013 3:18 PM
 Grasshopper 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 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 DiazLyntek, Inc
Post #1457311

 Permissions