CODEWITHSUNDEEP
phpmysql
EnterateNorte
EnterateNorte

Reputation: 173

MySQL sales per Day, Week and Month

Im trying to create a sales report in which the user can see sales per Day, Week and Month.

This is my table:

  CREATE TABLE IF NOT EXISTS `sales_act` (
  `id` int(11) NOT NULL auto_increment,
  `sale` decimal(10,2) NOT NULL default '0.00',
  `paymethod` smallint(2) NOT NULL default '0',
  `saledate` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` smallint(2) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `sales_act` (`id`, `sale`, `paymethod`, `saledate`, `status`) VALUES
(1, '150.00', 3, '2016-07-30 14:37:25', 2),
(2, '50.00', 1, '2016-08-14 21:38:34', 1),
(3, '150.00', 3, '2016-08-15 14:23:21', 2),
(4, '100.00', 1, '2016-08-15 14:25:12', 1),
(5, '50.00', 2, '2016-08-15 14:27:31', 3);

I was reading here in stackoverflow some examples of this, but I cant seem to make it work, this is what I have so far, but doesnt work :(

$result = $mysqli->query('SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month, SUM(sale) AS `salessum` WHERE `status` < 3 GROUP BY DATE_FORMAT(saledate, "%m-%Y")'); 
while ($row = $result->fetch_assoc()) {

    echo '<pre>';
print_r($row);
    echo '</pre>';

}

Can someone tell me what Im doing wrong? Thanks in advance!! :D

Upvotes: 1

Views: 1800

Answers (2)

Provie9
Provie9

Reputation: 379

You have forget to specify the database in this Line:

$result = $mysqli->query('SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month, SUM(sale) AS `salessum` WHERE `status` < 3 GROUP BY DATE_FORMAT(saledate, "%m-%Y")');

Better:

$result = $mysqli->query('SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month, SUM(sale) AS `salessum` FROM sales_act WHERE `status` < 3 GROUP BY DATE_FORMAT(saledate, "%m-%Y")'); 

SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month, 
SUM(sale) AS `salessum` 
FROM sales_act
WHERE status < 3 
GROUP BY DATE_FORMAT(saledate, "%m-%Y")

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You missed FROM clause should use MONTH (you have already saledate as datetime)

'SELECT MONTH(saledate) AS Month, SUM(sale) AS `salessum` 
 FROM sales_act 
 WHERE `status` < 3 GROUP BY MONTH(saledate)'

Upvotes: 2

Related Questions