mysql selecting conditional values

Question

I have a billing table where i have a PurchaseDate, ItemType, ItemSize, and other details.

billing table

+--------------------------------------------+
| PurchaseDate | ItemType | ItemSize | price |
+--------------------------------------------+
| 1-Jan-2015   | Jumper   | S        | 20    |
| 1-Jan-2015   | Jumper   | S        | 20    |
| 1-Jan-2015   | Jumper   | M        | 20    |
| 1-Jan-2015   | Jumper   | L        | 20    |
| 1-Jan-2015   | Shirt    | M        | 15    |
| 1-Jan-2015   | Shirt    | M        | 15    |
| 2-Jan-2015   | Shirt    | L        | 20    |
+--------------------------------------------+
...

ItemType are fixed and can be Jumper or Shirt. ItemSize are fixed and can be S, M or L.

What I need is to display a summary of purchases items ordered by PurchaseDate, followed by counts of every combination that exist for each date.

example output

+----------------------------------------------------------------------------------+
| Date       | Jumper[S] | Jumper[M] | Jumper[L] | Shirt[S] | Shirt[M] | Shirt[L]  |
+----------------------------------------------------------------------------------+
| 1-Jan-2015 | 2         | 1         | 1         | 0        | 1         | 0        |
| 2-Jan-2015 | 1         | 5         | 0         | 1        | 3         | 3        |
| 3-Jan-2015 | 0         | 0         | 0         | 0        | 0         | 0        |
| 4-Jan-2015 | 0         | 3         | 1         | 1        | 2         | 2        |
+----------------------------------------------------------------------------------+

Is this possible using a mysql query?

seahawk · Accepted Answer · 2015-08-26 00:20:17Z

1

Yes it is possible.

select PurchaseDate, 
sum(if(ItemType="Jumper" AND ItemSize="S",1,0)) as "Jumper[S]",
sum(if(ItemType="Jumper" AND ItemSize="M",1,0)) as "Jumper[M]",
sum(if(ItemType="Jumper" AND ItemSize="L",1,0)) as "Jumper[L]",
sum(if(ItemType="Shirt" AND ItemSize="S",1,0)) as "Shirt[S]",
sum(if(ItemType="Shirt" AND ItemSize="M",1,0)) as "Shirt[M]",
sum(if(ItemType="Shirt" AND ItemSize="L",1,0)) as "Shirt[L]"

from TableName
group by PurchaseDate;

Case: If you need all the date of a month, create a table with one column contains all dates. Let name of the table be "datetable" and column name be "datecolumn.

select t1.datecolumn, 
sum(if(ItemType="Jumper" AND ItemSize="S",1,0)) as "Jumper[S]",
sum(if(ItemType="Jumper" AND ItemSize="M",1,0)) as "Jumper[M]",
sum(if(ItemType="Jumper" AND ItemSize="L",1,0)) as "Jumper[L]",
sum(if(ItemType="Shirt" AND ItemSize="S",1,0)) as "Shirt[S]",
sum(if(ItemType="Shirt" AND ItemSize="M",1,0)) as "Shirt[M]",
sum(if(ItemType="Shirt" AND ItemSize="L",1,0)) as "Shirt[L]"

from datetable  t1 left join TableName  t2 on ( date(t1.datecolumn)=date(t2.PurchaseDate))

Where date(t1.datecolumn) between <date_1> and <date_2> // optional if you want data between two dates

group by t1.datecolumn;

Note: Code is not tested. Kindly let me know if any syntactic error found.

edited Aug 26, 2015 at 0:20

answered Aug 23, 2015 at 1:53

seahawk

1,92213 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

nads Over a year ago

Thanks for that Seahawk. It worked a treat. Is there a way to have a row for all the dates (even where there are no values like 3-Jan in the example above?

seahawk Over a year ago

@nads : can you create a dummy table that has only one column that contains all date of the month?

seahawk Over a year ago

@nads I have updated answer as per your need. You can filter data based on year or month or between two dates. Let me know if you find any issue.

nads Over a year ago

That worked! (although I had to replace the select PurchaseDate, and group by PurchaseDate to select datecolumn and group by datecolumn. Many thanks Seahawk!

seahawk Over a year ago

@nads : yes, you are absolutely right. I forgot to replace while editing the code. I have updated it. :)

|

Collectives™ on Stack Overflow

mysql selecting conditional values

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related