MYSQL: CROSSTAB QUERY BY SUPPLIER BY DATE IN BETWEEN TOTAL COUNT

SELECT DATE,
COUNT(IF(trans_supplier_name = ‘[Supplier Name1]’,1,NULL)) [Supplier_Name1],
COUNT(IF(trans_supplier_name = ‘[Supplier Name2]’,1,NULL)) [Supplier_Name2],
COUNT(IF(trans_supplier_name = ‘[Supplier Name3]’,1,NULL)) [Supplier_Name3],
COUNT(IF(trans_supplier_name = ‘[Supplier Name4]’,1,NULL)) [Supplier_Name4],
COUNT(IF(trans_supplier_name = ‘[Supplier Name5]’,1,NULL)) [Supplier_Name5],
COUNT(IF(trans_supplier_name = ‘[Supplier Name6]’,1,NULL)) [Supplier_Name6],
COUNT(IF(trans_supplier_name = ‘[Supplier Name7]’,1,NULL)) [Supplier_Name7],
COUNT(id) as TOTAL_COUNT
FROM sales_transactions
WHERE date BETWEEN ‘2013-01-01’ AND ‘2013-01-02’ AND ??????? = ‘??????????????’ GROUP BY date ASC

OUTPUT:

DATE Supplier_Name1……………………………………………….Supplier_Name7 TOTAL_COUNT
2013-01-01 2 9 2 2 52 2 5 74
2013-01-02 11 9 1 5 56 10 5 97

Advertisements
This entry was posted in MYSQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s