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

  `SDate` varchar(255) NOT NULL,
  `Grand_total` varchar(255) NOT NULL,
  `Payment_type` varchar(7) NOT NULL

-- 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


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'>
       <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>


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


    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 "<BR><b>Grand Total: $Grandtotal";


Hope you that you can help me


Recommended Answers

All 3 Replies

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

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?

Got It..

I use IF statement inside the query like this

    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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.