• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

Oracle SQL - getting a filtered total along with a total overall

I have 3 tables in Oracle 12:

TABLE:Product_Sales
SalesmanID
Product_ID
Sales_Amt

TABLE: Salesman_INFO
SalesmanID
salesman_type


TABLE:Products
Product_ID
product_type


I want to see, for a each sales person type, what % of the sales (by count)
is from a product_type that begins with 'BLUE'.
I use the query below to determine the total of 'BLUE' for each sales person type, but I dont
know how to incorporate the total sales of all irregardless of 'BLUE' so I can calc the percentage:
(Sales of 'Blue')/(Sales of All)

I would like to do it in one SQL if possible.
Any ideas?


Here's the query for the total of 'BLUE%' :

SELECT
B.Salesman_type
,COUNT(DISTINCT A.SalesmanID) AS Salesman
,median(a.NumSales) as Median_Sales
,count(*) as Num_Sales
,round((sum(A.NumSales)/count(*)),0) as Avg_Num_Sales
,sum(A.Sales_Amt) as Sales_Amt


from Product_Sales a
left join Salesman_INFO b on A.SalesmanID = B.SalesmanID
WHERE
Product_ID in
(select Product_ID from Products
where product_type LIKE 'BLUE%')
AND b.Salesman_type IS NOT NULL
GROUP BY B.Salesman_type
ORDER BY sum(a.Sales_Amt) DESC;
0
GNOVAK
Asked:
GNOVAK
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
SELECT
B.Salesman_type
,COUNT(DISTINCT A.SalesmanID) AS Salesman
,median(a.NumSales) as Median_Sales
,count(*) as Num_Sales
,round((sum(A.NumSales)/count(*)),0) as Avg_Num_Sales
,sum(A.Sales_Amt) as Sales_Amt

,sum(case when a.product_type Like 'BLUE%' then 1 else  0 end) as Num_Sales_Blue
,round((sum(  case when a.product_type Like 'BLUE%' then A.Sales_Amt else  0 end  )/ sum(case when a.product_type Like 'BLUE%' then 1 else  0 end)  ),0) as Avg_Num_Sales_Blue
,sum(case when a.product_type Like 'BLUE%' then A.Sales_Amt else 0 end) as Sales_Amt_Blue

from Product_Sales a
left join Salesman_INFO b on A.SalesmanID = B.SalesmanID
AND b.Salesman_type IS NOT NULL

JOIN Products p
  ON p.product_id = a.product_id 

GROUP BY B.Salesman_type
ORDER BY sum(a.Sales_Amt) DESC; 

Open in new window

0
 
GNOVAKAuthor Commented:
Thanks!
0
 
GNOVAKAuthor Commented:
Question -
I just found out that the Products table has more than one product_id. There is a Last_change_date field in addition to the product_id, product_type.

How do I change the (Products P) join to get the latest Last_change_date for ech product_id?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
JOIN Products p
  ON p.product_id = a.product_id
 AND p.last_change_date = ( SELECT MAX (x.last_change_date) FROM Products x
              WHERE x.product_id = a.product_id  )
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now