0

My data has total 100 rows 

Day         Item    Price Unit
01/01/17    jeans     15   4
01/02/17    shirt     10   3
01/01/17    skirt     20   7
01/04/17    skirt     25   1
02/01/17    jeans     5   4
02/02/17    shirt    15   3
02/01/17    skirt     24   7
03/04/17    skirt     25   5

I want ro represent my data as monthly sale by item in january and february

data should represent as 

month jeans skirt shirt total
Jan
Feb

I am getting error in my code 

SELECT MONTH,
MAX(CASE WHEN ITEM = 'JEANS' THEN GRANDTOTAL END) GRANDTOTAL,
MAX(CASE WHEN ITEM = 'SHIRT' THEN GRANDTOTAL END) AS GRANDTOTAL,
MAX(CASE WHEN ITEM = 'SKIRT' THEN GRANDTOTAL END) AS GRANDTOTAL, 
FROM SALES 
GROUP BY MONTH ;

I tried all methods but getting error, please help :)

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
KAIRA
  • 1
  • 1
  • 2
    Possible duplicate of [How to group by month from Date field using sql](https://stackoverflow.com/questions/14565788/how-to-group-by-month-from-date-field-using-sql) – Joshua R. Mar 31 '18 at 23:38
  • I am getting error in my code SELECT MONTH, MAX( CASE WHEN ITEM = 'JEANS' THEN GRANDTOTAL END) GRANDTOTAL, MAX( CASE WHEN ITEM = 'SHIRT' THEN GRANDTOTAL END) AS GRANDTOTAL, MAX( CASE WHEN ITEM = 'SKIRT' THEN GRANDTOTAL END) AS GRANDTOTAL, FROM SALES GROUP BY MONTH ; – KAIRA Mar 31 '18 at 23:42
  • What error are you getting? – Joshua R. Mar 31 '18 at 23:47
  • You ought to accept @âńōŋŷXmoůŜ's contribution if it answers your question. – Joshua R. Apr 01 '18 at 04:32

1 Answers1

0

Sales per item is defined as the sum of price x unit while total sales is the sum of sales for all items.

see demo: http://sqlfiddle.com/#!9/2a9e8f/1

SELECT date_format(day, '%b') as `month`,
sum(case when item='jeans' then price*unit else 0 end) as jeans,
sum(case when item='shirt' then price*unit else 0 end) as shirts,
sum(case when item='skirt' then price*unit else 0 end) as skirts,
sum(price*unit) as total
FROM myTbl
where month(day) <= 2
GROUP BY date_format(day, '%b')
ORDER BY month(day);

result:
month   jeans   shirts  skirts  total
Jan       60      30         165         255
Feb   20        45       168         233
jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
  • I am getting error, from keyword not found where expected, also need data only for jan and feb. Here price represents sales – KAIRA Apr 01 '18 at 03:19
  • I updated my answer. Also it has no syntax error since it has a sample dta and result in sqlfiddle.com: http://sqlfiddle.com/#!9/2a9e8f/1. Pls make sure you copy and paste properly – jose_bacoy Apr 01 '18 at 03:39
  • Hey, Thanks for code, I got it, but as the price itself is sales value and I am thinking that it should be sum(price) as aggregate. Can you tell me on that. – KAIRA Apr 01 '18 at 04:05
  • if you define sales as price value then you are correct and sum(price) is correct. if you like my amswer pls accept it by clicking on check button – jose_bacoy Apr 01 '18 at 04:07
  • Hey, I am getting invalid character error, can it be because of hypens on month in first line of command, code works fine on fiddle, but when i am running it in apex.oracle, I get invalid character error.. please guide me on that – KAIRA Apr 01 '18 at 04:46
  • it is called caret. you can remove it and use "month" instead. If still not working try, reporting_month. The word Month is a reserved word so you need to put an escape characters Or name it differently. – jose_bacoy Apr 01 '18 at 11:16