[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Displaying a non-aggregate column along with aggregate columns

Posted on 2012-09-20
3
Medium Priority
?
415 Views
Last Modified: 2012-09-21
Hello experts.

I am working on a seemingly simple SQL query, and I am having some difficulty figuring it out. This may be simple, but I am just not getting it. Hopefully someone can help.

I have the following tables (this is an example):


Table 1: Salesperson

ID      Name      Age      Salary
============================
1      Abe          61      140000
2      Bob              34      44000
5      Chris      34      40000
7      Dan              41      52000
8      Ken              57      115000
11      Joe      38      38000

Table 2: Orders

Number      order_date      cust_id      salesperson_id      Amount
==========================================================
10               8/2/96                  4                       2                         540
20               1/30/99                  4                       8                         1800
30               7/14/95              9                   1                        460
40               1/29/98                  7                       2                         2400
50               2/3/98                  6                       7                         600
60               3/2/98                  6                       7                         720
70               5/6/98                  9                       7                         150
80               4/3/96                  4                       2                         250

I need my query to return the following:  List the top sales person for each year. Include the year, sales person name and total sales (sort  ascending, by year).

My query, right now, looks like this:

select
      temptable.Sales_Year,
      temptable.Sales_Person,
      TempTable.Total_Sales Total_Sales
from
      (
      select
            year(ORD.order_date) Sales_Year,
            SP.Name Sales_Person,
            sum(ORD.amount) Total_Sales
      from
            orders ORD
            left join salesperson sp (nolock) on sp.salesperson_id=ord.salesperson_id
      group by
            year(ORD.order_date),
            SP.Name
      ) TempTable
--group by
--      TempTable.Sales_Year,
--      TempTable.Sales_Person,
--      temptable.Total_Sales


The results of this query above are:

Sales_Year    Sales_Person   Total_Sales
====================================
1995               Abe                      460.00
1996               Bob                      790.00
1998               Bob                      2400.00
1998               Dan                      1470.00
1999               Ken                      1800.00

As you can see, this is almost what I need.  1998 has two different sales people who are at the top, but I need the #1 sales person (who is Bob, in this example).

I understand that since the Sales_Person column contains varchar(50) values, which are not aggregate, SQL doesn't like me trying to group by year (that's why the last 2 lines of the query are commented out).

So, how do I accomplish what I need?  Ideally, 1998 would have only one entry with "Bob" being the top sales person.

Thanks, all.
0
Comment
Question by:sshani
3 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38419408
This probably isn't the prettiest way to do this, but it works.

select a.sales_year, 
a.sales_person,
c.total_sales
from
(select
year(ORD.order_date) Sales_Year,
SP.Name Sales_Person,
sum(ORD.amount) Total_Sales
from
orders ORD
left join Sales_Person sp on sp.salesperson_id=ord.salesperson_id
group by year(ORD.order_date) ,
SP.Name) a

inner join

(select Sales_Year, max(Total_Sales) as Total_Sales from
(
select
year(ORD2.order_date) Sales_Year,
sum(ORD2.amount) Total_Sales
from orders ORD2 
left join Sales_Person sp on sp.salesperson_id=ord2.salesperson_id
group by year(ORD2.order_date), sp.salesperson_id      
) b
group by Sales_Year) C

on a.sales_year = c.sales_year and a.Total_Sales = c.Total_Sales

Open in new window

0
 
LVL 18

Accepted Solution

by:
deighton earned 2000 total points
ID: 38420905
SELECT Sales_Year, Sales_Person, Total_Sales Total_Sales 
FROM 
(
select
      temptable.Sales_Year,
      temptable.Sales_Person,
      TempTable.Total_Sales Total_Sales,
      rank() OVER (PARTITION BY temptable.Sales_Year ORDER BY  TempTable.Total_Sales DESC) ROWN
from
      (
      select
            year(ORD.order_date) Sales_Year,
            SP.Name Sales_Person,
            sum(ORD.amount) Total_Sales
      from
            orders ORD
            left join salesperson sp (nolock) on sp.salesperson_id=ord.salesperson_id
      group by
            year(ORD.order_date),
            SP.Name
      ) TempTable
) DQ 
WHERE DQ.ROWN = 1     

Open in new window

0
 

Author Closing Comment

by:sshani
ID: 38423168
deighton,

Thanks for the solution. It is what I was looking for. I like the partition approach over the Union approach of Jared (although, both work).
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

872 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