1

I will start with the table: enter image description here Query:

SELECT taw.user_id AS taw_user_id, COALESCE(SUM(tai.bid), 0) AS tai_bid, taw.win AS taw_win
FROM tb_auction_winners taw JOIN
     tb_aukciono_istorija tai
     ON taw.id = tai.aukciono_id
WHERE tai.user_id = 206 AND taw.user_id = 206
GROUP BY aukciono_id, taw.user_id;

My problem is that I cannot get my desired result (changed using inspect element): enter image description here

It should sum all tai_bid and taw_win values where taw_user_id is same. I have read SQL query to sum the data and How to take sum of column with same id in SQL? threads but it did not help. Using GROUP BY aukciono_id, taw_user_id everything should be fine but it does not work. I guess it is a very easy fix but I cannot find what is wrong :( Help me, please.

SQL FIDDLE: http://sqlfiddle.com/#!9/6945a/1

Community
  • 1
  • 1
Lukas Naujokaitis
  • 109
  • 12

2 Answers2

3

If i got it right: you just want to sum by taw_user_id - so thats the only column in the GROUP BY clause

  SELECT taw.user_id AS taw_user_id
      ,SUM(taw.win) AS taw_win
      ,SUM(tai.bid) AS tai_bid
  FROM tb_auction_winners taw
  INNER JOIN  
  ( SELECT aukciono_id, user_id, SUM(bid) as bid
      FROM tb_aukciono_istorija
      group by aukciono_id, user_id
   )  tai
  ON taw.id = tai.aukciono_id
  AND taw.user_id  = tai.user_id
 WHERE taw.user_id = 206 
 GROUP BY taw.user_id
Esteban P.
  • 2,789
  • 2
  • 27
  • 43
  • Thank you for your answer :) I have tried that, I don't know why but it doesn't return what I want. http://imgur.com/fvu7QSz – Lukas Naujokaitis Jun 20 '17 at 11:50
  • could you explain it more exactly please? would be easier to help what do you expect exactly? what do you receive currently? Maybe you can create a little SQLFiddle https://stackoverflow.com/questions/38899464/what-is-sqlfiddle-and-why-should-i-care – Esteban P. Jun 20 '17 at 12:01
  • @LukasNaujokaitis: now i got it - i just updated my answer above through the join the rows doubled before. thats why i aggregate the data from tb_aukciono_istorija by aukciono_id and user_id, before joining with data from tb_auction_winners – Esteban P. Jun 20 '17 at 12:42
2

Check this Query

   select taw_user_id,sum(taw_win) as taw_win,sum(tai_bid) as tai_bid from
    (
    select taw.user_id AS taw_user_id,sum(win) as taw_win ,(select sum(bid) from tb_aukciono_istorija
    where user_id=taw.user_id and taw.id =aukciono_id ) AS tai_bid from tb_auction_winners   taw where user_id=206
    group by  taw.id)   temp

enter image description here

http://sqlfiddle.com/#!9/6945a/86

user1960808
  • 112
  • 4