MySQL SUM and return multiple other rows results

Question

I have following MySQL Tables:

TABLE: reg_users

-----------------
  id | username 
-----------------
  1     a
  2     b
  3     c

TABLE: bill

-----------------------
  id | m12     | p12 |
-----------------------
  1     13.69     1
  2     0.00      1
  3    269.89     1

SELECT b.p12 AS payed, 
       d.id, 
       b.m12 AS prev_month, 
       b.m12 AS curr_month, 
       username, 
       SUM(b.m12) AS total_prev_month 
FROM `reg_users` d 
LEFT JOIN `bill` b ON d.id = b.id 
ORDER BY d.`id` DESC;

I only get one row result and need to get all results (3 rows result with total_prev_month SUMMED together for each row so this row need to have values for each row 283.58.

Using GROUP BY i get correct total_prev_month value but i get only one row results...and i need in this example 3 rows results like:

------------------------------------------------
  payed | id     | username |  total_prev_month
------------------------------------------------
  1        1         a              283.58
  1        2         b              283.58
  1        3         c              283.58

window analytic functions such as SUM(b.m12) OVER () AS total_prev_month might be used for DB version 8.0, what's your DB version? — Barbaros Özhan
– Barbaros Özhan, Commented Jan 28, 2020 at 19:06
DB version is 7.3...above answer works great..thanks for help — John
– John, Commented Jan 28, 2020 at 19:07

Barbaros Özhan · Accepted Answer · 2020-01-28 19:06:19Z

1

Just use a subquery within the SELECT-list :

SELECT b.p12 AS payed, d.id, username, 
       (SELECT ROUND(SUM(m12),2) FROM `bill` ) AS total_prev_month 
  FROM `reg_users` d 
  JOIN `bill` b ON d.id = b.id 
 ORDER BY d.id

Demo

edited Jan 28, 2020 at 19:06

answered Jan 28, 2020 at 18:41

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL SUM and return multiple other rows results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related