2

I have two different Select statements which are producing correct results, but I'm curious if this can be done in one Select?

SELECT UserInfo.Id, AVG(BalanceInfo.Bet) as 'Avg bet'
FROM [USERINFO] as UserInfo
JOIN [BALANCEINFO] as BalanceInfo
ON UserInfo.Id = BalanceInfo.UserId
WHERE UserInfo.RoomId = 84 AND BalanceInfo.Bet != 0
GROUP BY UserInfo.Id

SELECT UserInfo.Id, SUM(BalanceInfo.Profit) as 'Deposits'
FROM [USERINFO] as UserInfo
JOIN [BALANCEINFO] as BalanceInfo
ON UserInfo.Id = BalanceInfo.UserId
WHERE UserInfo.RoomId = 84 AND BalanceInfo.ChangeType = 8 AND BalanceInfo.Profit > 0
GROUP BY UserInfo.Id

As you may see the difference is in Where statements.

The first Select produces average bets for every user and the second Select produces sum of the deposits for every user, which are two different tables. Can it be done in one instead?

Marat Adiev
  • 45
  • 1
  • 5

2 Answers2

3

You could use CASE statements in your aggregate functions:

SELECT UserInfo.Id, 
    AVG(CASE WHEN BalanceInfo.Bet != 0 THEN BalanceInfo.Bet ELSE NULL END) as 'Avg bet',
    SUM(CASE WHEN BalanceInfo.ChangeType = 8 AND BalanceInfo.Profit > 0 
        THEN BalanceInfo.Profit ELSE NULL END) as 'Deposits'
FROM [USERINFO] as UserInfo
JOIN [BALANCEINFO] as BalanceInfo
ON UserInfo.Id = BalanceInfo.UserId
WHERE UserInfo.RoomId = 84
GROUP BY UserInfo.Id

The main point here is to make sure to provide only matching values to aggregate functions, and NULL in other case. NULL values are ignored in aggregate functions, so only your desired values are calculated

dotnetom
  • 24,551
  • 9
  • 51
  • 54
  • You are 100% right, it's amazing how fast I got the answer and how long I've been striving on this. Thank you. I will dig more into this topic! – Marat Adiev Feb 27 '16 at 20:19
  • Is it possible to return the results of different CASE statements on the same row (for each single unique identifier in the ID column)? When I use CASE in this way, the SELECT query splits up the results of the same unique identifier on different rows. – Johan Jan 09 '23 at 13:01
0

You are using two different tables to get and sum. Below query is help you to get that.

SELECT UserInfo.Id, AVG(BalanceInfo.Bet) as 'Avg bet',SUM(BalanceInfo.Profit) as 'Deposits' FROM [USERINFO] as UserInfo JOIN [BALANCEINFO] as BalanceInfo ON UserInfo.Id=BalanceInfo.UserId WHERE UserInfo.RoomId = 84 AND BalanceInfo.Bet != 0 AND BalanceInfo.ChangeType = 8 AND BalanceInfo.Profit > 0 GROUP BY UserInfo.Id

Vadivel M
  • 11
  • 3
  • I did that and it gives another result, not what I need exactly. What I needed is perfectly satisfied by 'dotnetom' aka the first to answer the question :) – Marat Adiev Feb 29 '16 at 10:22