JohhnyNewbie February 2016

interval statistic with month name column

Last time I used interval, but I don't think it work like I want to, I just want to declare year and month. Can any guru can teach me here?

This is sample data as request:

food_type | sales | date_sales
   pizza     buy     02-01-2015 12:00:00
   pizza     buy     02-01-2015 03:00:00
   pizza     sell     03-01-2015 01:00:00
   pizza     sell     04-01-2015 05:00:00
   pizza     buy     05-01-2015 02:00:00

This is sample I am trying to do

DECLARE @Startdate AS datetime = '02-01-2015',
 @Enddate AS datetime = '05-01-2015'


SELECT TYPE_FOOD, COUNT(CASE WHEN STATUS = 'BUY' THEN 1 END) AS [INCOMING], COUNT(CASE WHEN STATUS= 'SELL' THEN 1 END) AS [OUTGOING]
FROM Shop WHERE [Something_Date] BETWEEN @StartDate AND @EndDate

OR

SELECT TYPE_FOOD, COUNT(CASE WHEN STATUS = 'BUY' THEN 1 END) AS [INCOMING], COUNT(CASE WHEN STATUS= 'SELL' THEN 1 END) AS [OUTGOING],DATEDIFF(MONTH, DATE_OF_PURCHASE, @YEAR ) AS [YEAR]  
FROM Shop 
GROUP BY TYPE_FOOD, DATEDIFF(MONTH,DATE_OF_PURCHASE, @YEAR )

-- i cant use datediff because i want between date..

This the closest query I can get if using datediff...not year only anymore...

Food TYPE | MONTH | BUY | SELL 
 pizza       12     10     5       
 pizza       13      3     1       
 ....        ...     ..     ..     
 pizza       32     5      2        

How can I make the sql output like this:

Food TYPE | MONTH   |   BUY | SELL | 
 pizza       Feb-15     10    5        
 pizza       Mar-15     3     1       
 ....        ...       ..     ..     
 pizza       May-15     5     2   

Please help me guru, I am still very beginner in sql

Answers


scaisEdge February 2016

i think you need a sum and a group by type_food

 select TYPE_FOOD, date_format(date_sales, '%m -  %Y'),
 sum(case when sales = 'buy' then 1 else 0 end) as incoming,
 sum(case when sales = 'sell' then 1 else 0 end) as outgoing
 from shop
 group by type_food

Post Status

Asked in February 2016
Viewed 2,390 times
Voted 4
Answered 1 times

Search




Leave an answer