Sum and other SQL QUERY concern

[ehpratah]

Hi all

I need some advice making my query work properly. I already played around it a dozen of times and still can't get it work. So as my last option im here:) . what im trying to achieve is sum all amount of a particular rows and display the Grandtotal which is working fine but I get stuck in this last requirement which is.. see screenshot below as I cant really explain it properly..:D

here are my table dump

CREATE TABLE IF NOT EXISTS `info` (
  `SDate` varchar(255) NOT NULL,
  `Grand_total` varchar(255) NOT NULL,
  `Payment_type` varchar(7) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `info`
--

INSERT INTO `info` (`SDate`, `Grand_total`, `Payment_type`) VALUES
('2014-05-18', '100', 'Cash'),
('2014-05-18', '200', 'Cash'),
('2014-05-18', '200', 'Bill'),
('2014-05-19', '100', 'Credit ');

and my test page

<?php

include 'config.php';



    $query = ("select SDate, Payment_type, SUM( Grand_total ) AS 'total_daily' from info WHERE SDate BETWEEN '2014-05-18' AND '2014-05-19'  GROUP BY `SDate`");


$string = '';
echo "<table border='1' width='50%' color='green'>
      <tr>
       <th width='5%' align='center'>Trans. Date</th>
      <th width='5%' align='center'>Cash</th>
      <th width='5%' align='center'>Credit Card</th>
      <th width='5%' align='center'>Invoice</th>
      <th width='5%' align='center'>Daily Sales</th>

      </tr>";


if ($result=mysqli_query($con,$query))
    {

    $Grandtotal=0;

    while($rows = mysqli_fetch_assoc($result))
        {


            $Grandtotal += $rows['total_daily'];

            $string .= "<tr>
            <td align='center'>".date("M d, Y", strtotime($rows['SDate']))."</td>
            <td align='center'></td>
            <td align='center'></td>
            <td align='center'></td>
            <td align='center'>".$rows['total_daily']."</td></tr> ";

        }

    } 



    echo $string;



echo"</table><br><br>";
echo "<BR><b>Grand Total: $Grandtotal";

?>

Hope you that you can help me

thanks
Neil

[pritaeas]

I think you need to add Payment_Type to your group by clause.

[ehpratah]

Hi Do you mean

 ("select SDate, Payment_type, SUM( Grand_total ) AS 'total_daily' from info WHERE SDate BETWEEN '2014-05-18' AND '2014-05-19'  GROUP BY `SDate`, Payment_type")

Still not getting it.

How can i put it in my table? normally you just put the rows value like $rows['Cash'] $rows['Credit'] and $rows['Bill'] in your TD. but for me this rows doesnt exist. when i tried adding the payment type in the Group by clause the group by date is not working. what am i doing wrong?

[ehpratah]

Got It..

I use IF statement inside the query like this

SELECT  SDate,
    SUM(IF(Payment_type = 'Cash', Grand_total, 0)) AS 'Cash', 
    SUM(IF(Payment_type = 'Credit', Grand_total, 0)) AS 'Credit Card', 
    SUM(IF(Payment_type = 'Bill', Grand_total, 0)) AS 'Bill', 
    SUM(Grand_total) AS Total FROM info WHERE SDate BETWEEN '2014-05-18' AND '2014-05-19' GROUP BY SDate