June 15, 2017 at 8:06 am
Hi guys,
As you can see, my query below displays the revenue by name for those who active.
Select First_Name, SUM(Total_Price*Num_Of_Products) from employees_new as E Inner join sales as S
on E.Employee_id=S.Employee_Id
Where Active = 'yes' Group By First_Name Order By SUM(Total_Price*Num_Of_Products) DESC
Now i want to display another thing in addition to what is displayed :
Those who aren't active ( active = 'no' ) their revenue : (SUM(Total_Price*Num_Of_Products))
will be displayed and added to employee number 4
I mean it sholud not change the tables, only display it in the query as revenue for employee_code = 4 or Employee name = inv (doesnt really matter)
Many Thanks
June 15, 2017 at 8:16 am
tal56490 - Thursday, June 15, 2017 8:06 AMHi guys,As you can see, my query below displays the revenue by name for those who active.
Select First_Name, SUM(Total_Price*Num_Of_Products) from employees_new as E Inner join sales as S
on E.Employee_id=S.Employee_Id
Where Active = 'yes' Group By First_Name Order By SUM(Total_Price*Num_Of_Products) DESCNow i want to display another thing in addition to what is displayed :
Those who aren't active ( active = 'no' ) their revenue : (SUM(Total_Price*Num_Of_Products))
will be displayed and added to employee number 4
I mean it sholud not change the tables, only display it in the query as revenue for employee_code = 4 or Employee name = inv (doesnt really matter)Many Thanks
Something like this?SELECT
First_Name
, Revenue = SUM(Total_Price * Num_Of_Products)
FROM
employees_new E
JOIN sales S ON E.Employee_id = S.Employee_Id
WHERE Active = 'yes'
GROUP BY First_Name
UNION
SELECT
First_Name = 'Inv'
, Revenue = SUM(Total_Price * Num_Of_Products)
FROM
employees_new E
JOIN sales S ON E.Employee_id = S.Employee_Id
WHERE Active = 'no'
GROUP BY First_Name
ORDER BY Revenue DESC;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 15, 2017 at 8:21 am
No DDL, sample data or expected results, so this is just a guess.
WITH ActiveInactive AS (
SELECT
CASE E.active
WHEN 'no' THEN 'inv'
WHEN 'yes' THEN E.First_Name
END AS First_Name
, S.Total_Price
, S.Num_Of_Products
FROM employees_new AS E
INNER JOIN sales AS S ON E.Employee_id = S.Employee_Id
)
SELECT
First_Name
, SUM(Total_Price*Num_Of_Products) AS Earnings
FROM ActiveInactive
GROUP BY First_Name
ORDER BY Earnings DESC
John
June 15, 2017 at 8:27 am
Phil Parkin - Thursday, June 15, 2017 8:16 AMtal56490 - Thursday, June 15, 2017 8:06 AMSomething like this?
SELECT
First_Name
, Revenue = SUM(Total_Price * Num_Of_Products)
FROM
employees_new E
JOIN sales S ON E.Employee_id = S.Employee_Id
WHERE Active = 'yes'
GROUP BY First_Name
UNION
SELECT
First_Name = 'Inv'
, Revenue = SUM(Total_Price * Num_Of_Products)
FROM
employees_new E
JOIN sales S ON E.Employee_id = S.Employee_Id
WHERE Active = 'no'
GROUP BY First_Name
ORDER BY Revenue DESC;
Yes exactly!
Thanks Phil
June 15, 2017 at 8:37 am
Thanks. John's version should work as well and may perform better, so you may like to check that.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply