CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
CleanX
CleanX

Reputation: 1176

Name of customer with highest sale monthwise

I have a sales table from which I select the total sales per month , highest sale , number of sale for all the months in the current year, using 

        select monthname(date),sum(amt_c),MAX(amt_c) 
        from sales where  year(date)= year(now())
        group by monthname(date) ;

I want to also select the customer who has done the highest purchase , i.e the customer correponding to the MAX(amt_c).

amt_c is the purchase done by the customer,

Upvotes: 1

Views: 1990

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

select v.monthname,
       v.sum_amt_c,
       v.max_amt_c,
       count(s.amt_c) as num_of_amounts,
       group_concat(s.cust) as customers
  from (select monthname(date) as monthname,
               sum(amt_c) as sum_amt_c,
               max(amt_c) as max_amt_c
          from sales
         where date between concat(year(now()), '-01-01') and concat(year(now()), '-12-31')
         group by monthname(date)) v
  join sales s
    on v.max_amt_c = s.amt_c
   and v.monthname = monthname(s.date)
   and s.date between concat(year(now()), '-01-01') and concat(year(now()), '-12-31')
 group by v.monthname, v.sum_amt_c, v.max_amt_c
 order by month(s.date)

This is similar to Andomar's answer however it provides the following benefits:

  1. If your DATE field is indexed (it should be) the above query will use that index. You should not have criteria on a date field with a function applied to it. MySQL does not support function based indexes, so it is a given that year(date) is not indexed. date may be indexed, however.

  2. This sorts the results by month # (1-12) but shows the month name.

  3. In the event that the same 2+ customers are tied, this will list all of them, and show only one row for that month. You would otherwise potentially have 2, 3, 4+ rows for a single month in the event of a tie. This is done via MySQL's GROUP_CONCAT function.

Upvotes: 1

Andomar
Andomar

Reputation: 238166

One way is a filtering join:

select  filter.mn 
,       filter.sum_sales
,       filter.max_sales
,       sales.cust
from    (
        select  monthname(date) as mn
        ,       sum(amt_c) as sum_sales
        ,       max(amt_c) as max_sales
        from    sales
        where   year(date) = year(now())
        group by
                mn
        ) filter
join    sales
on      monthname(sales.date) = filter.mn
        and sales.amt_c = filter.max_sales

For more approaches, browse the tag.

Upvotes: 2

Related Questions