Solved

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

Posted on 2014-03-14
4
466 Views
Last Modified: 2014-03-14
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
Comment
Question by:GNOVAK
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:GNOVAK
Comment Utility
Thanks!
0
 

Author Comment

by:GNOVAK
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now