how to achieve mysql joining with conditional statement

Question

Hello I am thirst to get help as I am stuck for two days on a complex logical query,if anybody can help to solve.

Order Table

id           | region_id   | created_at | sale 
=============|=============|=========================
1            | 1           | 2011-09-21 |  $250
2            | 2           | 2012-03-12 |  $320
3            | 1           | 2010-09-15 |  $300
4            | 2           | 2011-08-18 |  $180
5            | 1           | 2012-04-13 |  $130
6            | 3           | 2010-06-22 |  $360
7            | 2           | 2011-09-25 |  $330

Regions Table

id           | region_name  
=============|=============
1            | Region 1          
2            | Region 2      
3            | Region 3

Expected Output

What I have tried to achieve

select distinct `regions`.`region_name`, sum(orders.sale) as sum, 
CASE WHEN MONTH(orders.created_at)>=4 THEN
concat(YEAR(orders.created_at), '-',YEAR(orders.created_at)+1)
ELSE concat(YEAR(orders.created_at)-1,'-', YEAR(orders.created_at)) 
END AS financial_year from `orders` inner join `regions` on `orders`.`region_id` = `regions`.`id` group by YEAR(orders.created_at), `regions`.`region_name` order by `orders`.`region_id` asc,  YEAR(orders.created_at) asc

My Queries Output

Where is my logical problem in query,one thing data should be fetched financial year wise not only normal year wise.

Thanks

in your expected result you expect total sum to be: 960+260+560+340+140+280 = 2540. but in your data source you have only: 250+320+300+180+130+360+330=1870. that seems impossible to me. Another point is you check `MONTH(orders.created_at)>=4` but do not check `YEAR` but in sample provided you have dates marked 2010,2011,2012 so that logic seems not very smart. — Alex, May 26 '16 at 13:04
@Alex, yes you are right. I have prepared with demo data only that may not match with screen to screen but last point you ask it's ok actually I want to check if months fall down in financial year or not,on the basis of I concat the years to display by taking the years only from my timestamps field. — Tamaghna Banerjee, May 26 '16 at 13:14
Guide me through on a way that my query can be optimized and unnecessary logic can be removed if I have added already. — Tamaghna Banerjee, May 26 '16 at 13:19

Alex · Answer 1 · 2016-05-26T13:32:55.620

http://sqlfiddle.com/#!9/16fdfb/9

Just to fix your query you should not use GROUP BY YEAR since your financial year does not match to calendar year, and since you don't want output different financial year in different rows but in columns. You can transform your query to:

SELECT regions.region_name, 
    o.salePrev as `2010-11`,  
    o.saleCurrent as `2011-12`
FROM  (SELECT 
          region_id,
          SUM(IF(MONTH(orders.created_at)<4,sale,0)) salePrev,
          SUM(IF(MONTH(orders.created_at)>=4,sale,0)) saleCurrent
       FROM orders
       GROUP BY region_id
       ) o
INNER JOIN regions
ON o.region_id = regions.id;

But as I mentioned in my comment, your condition MONTH(orders.created_at)<4 is year independent I would transform it into something like:

SELECT regions.region_name, 
    o.salePrev as `2010-11`,  
    o.saleCurrent as `2011-12`
FROM  (SELECT 
          region_id,
          SUM(IF(
                (MONTH(orders.created_at)<4 && YEAR(orders.created_at) = 2012)
                || YEAR(orders.created_at) < 2012
            ,sale,0)) salePrev,
          SUM(IF(MONTH(orders.created_at)>=4 && YEAR(orders.created_at) = 2012,sale,0)) saleCurrent
       FROM orders
       GROUP BY region_id
       ) o
INNER JOIN regions
ON o.region_id = regions.id;

But yes it does not group by year, that just group current (2012-04 +) year against all the past years (2012-04 -).

If you need all years...

UPDATE http://sqlfiddle.com/#!9/16fdfb/17

SELECT r.region_name, 
    SUM(IF(o.f_year=2010,o.y_sale,0)) as `2010-11`,  
    SUM(IF(o.f_year=2011,o.y_sale,0)) as `2011-12`,
    SUM(IF(o.f_year=2012,o.y_sale,0)) as `2012-13`
FROM  (SELECT 
          region_id,
          IF(MONTH(orders.created_at)<4,YEAR(created_at)-1,YEAR(created_at)) f_year,
          SUM(sale) y_sale
       FROM orders
       GROUP BY region_id, f_year
       ) o
INNER JOIN regions r
ON o.region_id = r.id
GROUP BY r.id

As the years are dynamic and I can't set hard coded like 2012 or 2010 then what is the solution to make the query more dynamic that your query can run similar to my expected output screen? because it could be any years which lies in between financial years. — Tamaghna Banerjee, May 26 '16 at 13:26
the simple way is to hardcode (and I suggest you to harcode some years value. you can set them from php or any other language you use to call mysql). but if you want them dynamic in columns you should learn about pivot tables for mysql. for example here : http://stackoverflow.com/questions/7674786/mysql-pivot-table — Alex, May 26 '16 at 13:29

how to achieve mysql joining with conditional statement

1 Answers1