CODEWITHSUNDEEP
mysql
Aadrinmusic
Aadrinmusic

Reputation: 124

MySQL 2 queries combine into 1 query

So I have to query a DB table based on a certain type and would like to do it in 1 query only. Below are the queries I would like to combine:

  1. SELECT usr_id, SUM(coins) AS coinppv FROM coin_tbl WHERE coin_type = 1 GROUP BY usr_id
  2. SELECT usr_id, SUM(coins) AS coinmly FROM coin_tbl WHERE coin_type = 3 GROUP BY usr_id

Then I would also like to get the sum of coinppv and coinmly then finally GROUP BY usr_id.

I have tried doing something like the ff. but I don't get the result I wanted: (NOTE: this example did not include summing up coinppv and coinmly)

SELECT usr_id,
(SELECT SUM(coins) FROM `coin_tbl` WHERE coin_type = 1) AS coinppv,
(SELECT SUM(coins) FROM `coin_tbl` WHERE coin_type = 3) AS coinmly
FROM `coin_tbl` GROUP BY usr_id

I also looked into JOINing but I also didn't get the result I am looking for.

Upvotes: 1

Views: 70

Answers (4)

Kickstart
Kickstart

Reputation: 21513

If you want it in one row per user id, with both values then something like this should do it

SELECT usr_id, SUM(if(coin_type = 1, coins, 0)) AS coinppv, SUM(if(coin_type = 3, coins, 0)) AS coinmly
FROM coin_tbl 
GROUP BY usr_id

If you wanted to do it as a JOIN and assuming you have a table of users you could use something like the following

SELECT usr_id, IFNULL(Sub1.coinppv, 0), IFNULL(Sub2.coinmly, 0)
FROM user_table 
LEFT OUTER JOIN (SELECT usr_id, SUM(coins) AS coinppv FROM `coin_tbl` WHERE coin_type = 1 GROUP BY usr_id) Sub1 ON coin_tbl.usr_id = Sub1.usr_id
LEFT OUTER JOIN (SELECT usr_id, SUM(coins) AS coinmly FROM `coin_tbl` WHERE coin_type = 3 GROUP BY usr_id) Sub2 ON coin_tbl.usr_id = Sub2.usr_id

Upvotes: 1

DonCallisto
DonCallisto

Reputation: 29912

SELECT usr_id, coin_type, SUM(coins)
FROM coin_tbl 
WHERE coin_type = 1 OR coin_type = 3 
GROUP BY usr_id, coin_type

See it in action on SqlFiddle

Swapnesh answer (once he will edit it) is better as it use IN clause that is more readable if you have to control coin_type against "many" different type (say five or six), but if only two coin_type are involved, there isn't much difference

Upvotes: 2

Imre L
Imre L

Reputation: 6249

SELECT usr_id,
       SUM(IF(coin_type = 1,coins,0)) AS coinppv,
       SUM(IF(coin_type = 3,coins,0)) AS coinmly, 
       SUM(coins) AS total
FROM `coin_tbl` 
WHERE coin_type IN (1,3)
GROUP BY usr_id

total is sum of types 1 and 3, remove where if you need total of all

Upvotes: 2

swapnesh
swapnesh

Reputation: 26722

Use In

SELECT usr_id, SUM(coins) 
AS coinppv 
FROM coin_tbl
WHERE coin_type 
IN( 1,3) 
GROUP BY usr_id

Upvotes: 3

Related Questions