1

I want to calculate the division of each row per the sum of all rows that have the same Dateadded and fundid, but it seems my query is wrong due the results is not what I was expecting. My table schema looks like this, I avoided mine because it has many more columns:

+----+--------+------------+--------+
| id | fundid | Dateadded  | amount |
+====+========+============+========+
| 1  |  45    | 21-02-2018 |  5412  |
| 2  |  45    | 21-02-2018 |  5414  |
| 3  |  45    | 21-02-2018 |  1412  |
| 4  |  45    | 22-02-2018 |  5756  |
| 5  |  45    | 22-02-2018 |  4412  |
| 6  |  45    | 25-02-2018 |  2532  |
| 7  |  45    | 26-02-2018 |  7892  |
| 8  |  45    | 26-02-2018 |  8143  |
+----+-------+-------------+--------+

  • Rows with id's: 1,2,3 should be calculated together because they have the same fundid and date.

  • Rows with id's: 4,5 same thing.

  • Rows with id's: 6 it is just one.
  • Rows with id's: 7,8 same thing.

My SQL query:

SELECT fundid
     , Dateadded
     , ( amount / SUM(amount) ) AS AvgRow 
  FROM stock2 
 GROUP 
    BY fundid
     , Dateadded 
 ORDER 
    BY DateAdded ASC
Strawberry
  • 33,750
  • 13
  • 40
  • 57
H Aßdøµ
  • 2,925
  • 4
  • 26
  • 37

2 Answers2

1

Is this what you want?

select t.*, t.amount / tt.total_amount
from stock2  t join
     (select fundid, dateadded, sum(amount) as total_amount
      from stock2 t
      group by fundid, dateadded
     ) tt
     using (fundid, dateadded);

Or is this?

select fundid, dateadded, sum(t.amount) / tt.total_amount
from stock2  t cross join
     (select sum(amount) as total_amount
      from stock2 t
     ) tt
 group by fundid, dateadded, tt.total_amount;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Check out a very well explained response to a similar issue related to usage of Group by here).

Similarly to the situation described there, for your query is ambiguous re: what "amount" should be used for each row. I.e. if you try:

    SELECT fundid, Dateadded, ( AVG(amount) / SUM(amount) ) AS AvgRow FROM stock2 GROUP BY fundid, Dateadded ORDER BY DateAdded ASC

it will work because AVG(amount) is non-ambiguous for each (fundid, Dateadded) pair that should be calculated together.

It seems you are looking for something like:

   SELECT st.fundid, st.Dateadded, ( amount / st2.total) ) AS AvgRow 
   FROM stock2 st 
   inner join 
      (select fundid, Dateadded, sum(amount) as total
       from stock2
       GROUP BY fundid, Dateadded) st2 
   on st.fundid = st2.fundid and st.Dateadded = st2.Dateadded
   order by st.Dateadded
Elena P
  • 116
  • 1
  • 5