CODEWITHSUNDEEP
mysqlsql
user2981811
user2981811

Reputation: 35

How to get the sum of a specific user from two tables?

I currently have 2 tables:

Favorite: userID drinkName

History: userID drinkName

I want to get the sum of the total times a specific userID shows up in each table, and then then the total number of times userID shows up in both tables.

(SELECT COUNT(userID) AS totalDrinks FROM History
WHERE userID = 'sai') union 
(SELECT COUNT(userID) AS totalDrinks FROM Favorite
WHERE userID = 'sai')

So that code gets me the following output:

totalDrinks

4

2

However I am trying to use the MySQL sum function and that's not adding the two things up though. So I was wondering how I would rewrite my query to output 6?

Upvotes: 0

Views: 42

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Your UNION approach was almost there. You will have to SUM the result of both queries:

SELECT SUM(totalDrings) totalDrings FROM (
    SELECT COUNT(*) totalDrinks FROM History
    WHERE userID = 'sai'
    UNION ALL
    SELECT COUNT(*) FROM Favorite
    WHERE userID = 'sai'
) s

A few things to note. You should use UNION ALL otherwise if the COUNTs result in the same number then they will be added only once. Another thing is that you should not use an INNER JOIN in here as that will force the users to be present in both tables.

Upvotes: 0

Mihai
Mihai

Reputation: 26804

SELECT SUM(userID)as totalDrinks   FROM History h 
JOIN Favorite f ON h.userID=f.userID 
GROUP BY userID
WHERE userID = 'sai'

Upvotes: 1

Related Questions