CODEWITHSUNDEEP
mysqljoinsubquery
user518543
user518543

Reputation: 25

MySQL subquery in select

I have the following scenario. i have three tables (users, sales, sales_details) Users to Sales is a 1 to 1 relationship and sales to sales_details is 1 to many. I am running a query where I get all the sales for each user by joining all 3 tables without any issue. Query looks something like this

SELECT s.month as month,u.name as name, s.year as year, s.date as date,sum(sd.qty) as qty,sum(sd.qty*sd.value) as value,s.id as id,sum(sd.stock) as stock,s.currency as currency,s.user as user 
FROM sales as s     
left join sales_details as sd on s.id = sd.Sales 
inner join users as u on s.user = u.Id 
group by s.Id

What I want to do now is add an extra field in my query which will be a subquery.

SELECT SUM(total) AS total_yearly 
FROM (
    SELECT  sum(qty) as total 
    FROM sales 
    left join sales_details on sales.Id = sales_details.Sales 
    WHERE ((month <= MONTH(NOW()) and year = YEAR(NOW())) 
        or (month >= MONTH(Date_add(Now(),interval - 12 month)) and year = YEAR(Date_add(Now(),interval - 12 month)))) 
        and User = **ID OF USER** ) as sub

This query on its own gives me the sales for the user for the past 12 months while the original query does it per month. I know that the result will be the same for each user but i need it for other calculations. My problem is how I will join the 2 queries so that the subquery will read the user id from the original one.

Thanks in advance!

Upvotes: 0

Views: 66

Answers (1)

Barmar
Barmar

Reputation: 782693

Group the second query by user, and then join it with the original query.

SELECT s.month as month,u.name as name, s.year as year, s.date as date,
       sum(sd.qty) as qty,sum(sd.qty*sd.value) as value,s.id as id,
       sum(sd.stock) as stock,s.currency as currency,s.user as user,
       us.total
FROM sales as s     
left join sales_details as sd on s.id = sd.Sales 
inner join users as u on s.user = u.Id 
inner join (
    SELECT User, sum(qty) as total 
    FROM sales 
    left join sales_details on sales.Id = sales_details.Sales 
    WHERE ((month <= MONTH(NOW()) and year = YEAR(NOW())) 
        or (month >= MONTH(Date_add(Now(),interval - 12 month)) and year = YEAR(Date_add(Now(),interval - 12 month)))))
    GROUP BY User) AS us ON s.user = us.user
group by s.Id

Upvotes: 1

Related Questions