CODEWITHSUNDEEP
mysqlsqlselect
JulianoMartins
JulianoMartins

Reputation: 553

SQL Query to get the total sales per seller by month

I try this:

SELECT
  profile.user_id,
  profile.name,
  total_month.total as month10
FROM profile
  LEFT OUTER JOIN (SELECT
      order.seller_id,
      COUNT(*) AS total
    FROM order
    WHERE MONTH(order.data_hora) = 10
    GROUP BY order.seller_id) AS total_month
    ON total_month.seller_id= profile.user_id;

The result was this:

-------------------------
|user_id| name  |month10|
-------------------------
|   5   |user1  |  73   |
|   1   |user2  |   1   |
-------------------------

But I need more months like this:

-------------------------------------------------
| user_id | name  | month10 | month11 | month12 |
-------------------------------------------------
|    5    | user1 |   73    |    52   |    65   |
|    1    | user2 |   67    |    56   |    78   |
-------------------------------------------------

How could I do this without creating a function?

Upvotes: 0

Views: 6366

Answers (3)

JulianoMartins
JulianoMartins

Reputation: 553

It was perfect!

The field type 'data_hora' is datetime, so I made a small change.

SELECT p.user_id,
       p.name,
       tm.month10,
       tm.month11,
       tm.month12,
       (tm.month10+tm.month11+tm.month12) AS final_total
FROM profile p
LEFT OUTER JOIN
  (SELECT o.seller_id,
          sum(month(o.data_hora) = 10) AS month10,
          sum(month(o.data_hora) = 11) AS month11,
          sum(month(o.data_hora) = 12) AS month12
   FROM order o
   WHERE MONTH(ORDER.data_hora) IN (10, 11, 12)
   GROUP BY ORDER.seller_id ) tm 
   ON tm.seller_id = p.user_id
ORDER BY final_total DESC;

How could I optimize the field "final_total"?

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23361

Well as I said in the comments supporting the @Schalk comment, in order to get this working as you want you will need a function to get a DYNAMIC PIVOT TABLE or TRANSPOSE ROWS TO COLUMNS google it if you prefer.

For your solution I've created a query that gives you all months/values like this:

user_id  name  jan feb mar  apr  may jun jul aug sep oct nov dec
   1       a    0   0   1   10    0   0   1   2   7   2   3   0 
   2       b    1   0   0    0    2   0   0   0   3   1   1   0

If it fits to your problem this is your query:

select
    profile.user_id,
    profile.name,
    sum(if( MONTH(orderr.data_hora) = 1, 1, 0 )) as Jan,
    sum(if( MONTH(orderr.data_hora) = 2, 1, 0 )) as Feb,
    sum(if( MONTH(orderr.data_hora) = 3, 1, 0 )) as Mar,
    sum(if( MONTH(orderr.data_hora) = 4, 1, 0 )) as Apr,
    sum(if( MONTH(orderr.data_hora) = 5, 1, 0 )) as May,
    sum(if( MONTH(orderr.data_hora) = 6, 1, 0 )) as Jun,
    sum(if( MONTH(orderr.data_hora) = 7, 1, 0 )) as Jul,
    sum(if( MONTH(orderr.data_hora) = 8, 1, 0 )) as Aug,
    sum(if( MONTH(orderr.data_hora) = 9, 1, 0 )) as Sep,
    sum(if( MONTH(orderr.data_hora) = 10, 1, 0 )) as Oct,
    sum(if( MONTH(orderr.data_hora) = 11, 1, 0 )) as Nov,
    sum(if( MONTH(orderr.data_hora) = 12, 1, 0 )) as Dece
from 
    profile left join orderr 
                   on profile.user_id = orderr.seller_id
group by profile.user_id,
         profile.name

I've created a fiddle to it (but the data_hora column I created as integer to make it quickly to do it, it is for understanding).

http://sqlfiddle.com/#!2/4a1a2e/5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can extend your query to do what you want. Just be more flexible in the subquery:

SELECT p.user_id, p.name,
       tm.month10, tm.month11, tm.month12
FROM profile p LEFT OUTER JOIN
     (SELECT o.seller_id,
             sum(o.data_hora = 10) as month10,
             sum(o.data_hora = 11) as month11,
             sum(o.data_hora = 12) as month12
      FROM order o
      WHERE MONTH(order.data_hora) in (10, 11, 12)
      GROUP BY order.seller_id
     ) tm
     ON tm.seller_id = p.user_id;

Upvotes: 2

Related Questions