CODEWITHSUNDEEP
sqlsql-servert-sqlselectwhere-clause
Marat Adiev
Marat Adiev

Reputation: 45

Multiple SQL Select's with different Where's into one Select

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?

Upvotes: 2

Views: 113

Answers (2)

Vadivel M
Vadivel M

Reputation: 11

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

Upvotes: 0

dotnetom
dotnetom

Reputation: 24901

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

Upvotes: 3

Related Questions