CODEWITHSUNDEEP
sqloracle10g
Student
Student

Reputation: 387

Order total sales by month

I am printing out the total sales for each state so long as they have over $6000 in sales for that month. The results need to be ordered by month(descending order). I noticed that I got 2 results for Texas for the same month. So what that tells me is it is just looking for individual sales over $6,000 even though I have a sum(so.total) which I thought would give the total sales for state ordered by the month. Not sure how to break down the sales by each individual month if the order_date field has day-month-year in it.(Using Oracle 10g)

Input:

 SELECT c.state "State",to_char(so.order_date, 'Month')"Month", sum(so.total)"Sales"
 FROM a_customer c
 JOIN a_sales_order so ON c.customer_id=so.customer_id
 GROUP BY c.state, so.order_date
 HAVING sum(so.total)>6000
 ORDER BY to_char(so.order_date, 'MM') desc

Output:

State Month        Sales
----- --------- --------
TX    September   $8,400 
CA    July        $8,377 
TX    March       $7,700 
TX    March       $8,540 
CA    February   $46,370 
MN    February    $6,400 
CA    February   $24,650

Upvotes: 0

Views: 340

Answers (1)

Ollie
Ollie

Reputation: 17578

I think there may be an issue in both your GROUP BY and ORDER BY clauses.

Try:

SELECT c.state AS "State",
       to_char(so.order_date, 'Month') AS "Month", 
       sum(so.total) AS "Sales"
  FROM a_customer c
  JOIN a_sales_order so ON c.customer_id=so.customer_id
 GROUP BY c.state, 
       to_char(so.order_date, 'Month')
HAVING sum(so.total)>6000
 ORDER BY to_date(to_char(so.order_date, 'Month'), 'Month') desc

I don't have a working instance in front of me at the moment to try it but you need to ensure you are grouping on the same output as you are displaying, in your original example, you are grouping on the full order_date but displaying just the month portion. The order by in your example will order by the text of the month rather than the actual chronological order.

Hope it helps.

Upvotes: 1

Related Questions