Using CASE Statement when using two different statement if the condition is true

  1. I need to display the data retrieving from table Market wise. And for each Market, Number of Orders and No of Items can be calculated and displayed. I have only one column namely market, but i need to display market in horizontal manner as below.

Using CASE Statement when using two different statement if the condition is true

I have tried creating Temporary Table to achieve this. Anyhow i got the result, but is not accurate.

$MarketQry="SELECT DISTINCT `Market` FROM `orders`"; $MarketResult=mysql_query($MarketQry,$con); $OrdersQry=""; while($mvalue=mysql_fetch_array($MarketResult)) { $OrdersQry.="CASE WHEN `Market`='".$mvalue['Market']."' THEN SUM(`Count`) ELSE 0 END AS `".$mvalue['Market']." -Number of Orders`, CASE WHEN `Market`='".$mvalue['Market']."' THEN SUM(`Records`) ELSE 0 END AS `".$mvalue['Market']." -Number of Order Line Items`,"; } $createqry="CREATE TEMPORARY TABLE `temp_order_market` AS SELECT ".$OrdersQry."`OrderDate` FROM (SELECT COUNT(DISTINCT `OrderID`) AS `Count`,COUNT(`OrderID`) AS `Records`,`OrderDate`,`Market` FROM `orders` WHERE (`OrderDate` BETWEEN '$StartDate' AND '$EndDate') GROUP BY `OrderDate`) ) AS `dummy` GROUP BY `OrderDate` ORDER BY `OrderDate` DESC"; echo $createqry."<br /><br />"; $resQry=mysql_query($createqry,$con) or die(mysql_error($con)); $selectqry="SELECT * FROM `temp_order_market`";

  1. Whatever i am getting the result is not accurate. Since If i get the result for individual market i am getting the exact result. The following is the query i am using for getting result for individual market.

    SELECT SUM(`Count`) AS `Orders`,SUM(`Records`) AS `Records`,`OrderDate` FROM (SELECT COUNT(DISTINCT `OrderID`) AS `Count`,COUNT(`OrderID`) AS `Records`,`OrderDate` FROM `orders` WHERE (`OrderDate` BETWEEN '2015-01-01' AND '2015-01-31') AND `Market`='$MarketName' GROUP BY `OrderDate`)) AS `dummy` GROUP BY `OrderDate` ORDER BY `OrderDate` DESC";

Now my question is:

  1. Can we do this other than this method/Is this possible to achieve the method that i am using?
  2. I doubt the CASE Statement of the query I've written.

Please share your thoughts. thanks in advance.

Category:php Views:0 Time:2018-12-05
Tags: php mysql

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 6.209 (s). 12 q(s)