CODEWITHSUNDEEP
mysqlsql
Damien
Damien

Reputation: 95

sum values with multiple account

I'm trying to sum values for my sales. However, one user can have multiple account and each account have different sales record. Below is my table.

user_table 

user_id username
----------------
1       adam   
2       david
3       siva

account 

account user_id
-------------
001     1       
002     1       
003     1

sales 

account sales
-------------
001     20      
002     30      
003     10

How to sum all the sales for one user? please help me. Thanks

Upvotes: 1

Views: 104

Answers (5)

Karan Eyunni
Karan Eyunni

Reputation: 1

I would suggest something like this.

   select a.username as User_Names ,case when sum(sales) is NULL then 0 else 
    sum(sales) end as Total_Sales 
    from stack_users a 
    left join  stack_account b 
    on a.userid= b.userid 
    left join stack_sales c 
    on c.account = b.account
    group by a.username

Output:

+------------+-------------+
| User_Names | Total_Sales |
+------------+-------------+
| Adam       |  60         |
+------------+-------------+
| David      |  0          |
+------------+-------------+
| siva       |  0          |
+------------+-------------+

Upvotes: 0

user8406805
user8406805

Reputation: 

mysql> create table user_table(user_id int, username varchar(20));
Query OK, 0 rows affected (0.45 sec)

mysql> insert into user_table values
    -> (1,'adam'),
    -> (2,'david'),
    -> (3,'siva'); 
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table account(account varchar(20), user_id int);
Query OK, 0 rows affected (0.34 sec)

mysql> insert into account values
    -> ('001',1),       
    -> ('002',1),
    -> ('003',1);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table sales(account varchar(20),sales int);
Query OK, 0 rows affected (0.26 sec)

mysql> insert into sales values
    -> ('001',20),      
    -> ('002',30),
    -> ('003',10);
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select u.user_id,sum(sales) sales
    -> from user_table u
    -> natural join account
    -> natural join sales
    -> where u.user_id = 1; 
+---------+-------+
| user_id | sales |
+---------+-------+
|       1 |    60 |
+---------+-------+
1 row in set (0.00 sec)

Upvotes: 2

Vipul
Vipul

Reputation: 941

Here query with Subquery.Try this 

SELECT SUM(sales) FROM sales WHERE account IN (SELECT account FROM `account` where user_id = 1)

Upvotes: 2

Abdullah Al Shakib
Abdullah Al Shakib

Reputation: 2044

Try this

SELECT SUM(s.sales) AS total_sale 
FROM   sales s 
INNER JOIN account a ON a.account = s.account 
WHERE  a.user_id = 'USER_ID'

Upvotes: 1

Bhumi Shah
Bhumi Shah

Reputation: 9476

Here is the Query:

SELECT Sum(sales) 
FROM   sales s 
       INNER JOIN account a 
               ON a.account = s.account 
       INNER JOIN user_table u 
               ON u.user_id = a.user_id 
WHERE  u.user_id = 1

Upvotes: 1

Related Questions