1

I'm trying to build a SELECT that shows all the sum of the dates. Example:

2019-05-10 - 10.00
2019-05-10 - 10.00
2019-01-01 - 1.00

The loop need to show:

2019-05-10 - total: 20.00
2019-01-01 - total: 1.00

i already try but.. i need the SELECT SUM to complete it... I need to show the DATE + SUMValue.

$support_sales = $con->prepare("");                        
$support_sales->execute();
$support_sales->bind_result();
$support_sales->store_result();

while($support_sales->fetch()) {}

I tried

        $con = new mysqli($dbServername, $dbUsername, $dbPassword, $dbName);

        if ($con->connect_error) {
                die("Connection failed: " . $con->connect_error);
        } 

        $support_sales = $con->prepare("SELECT budget_date SUM(budget_price) FROM public_budgets WHERE budget_status = 1 GROUP BY budget_date");                        
        $support_sales->execute();
        $support_sales->bind_result($budget_date, $total);
        $support_sales->store_result();

        while($support_sales->fetch()) {

           echo $budget_date.$total; 
        }

error: Fatal error: Uncaught Error: Call to a member function execute() on boolean in /var/www/html/asp/inc/csv_excel_statistics_sales.php:47 Stack trace: #0 {main} thrown in /var/www/html/asp/inc/csv_excel_statistics_sales.php on line 47

user21312321
  • 9
  • 4
  • Could you show us the SQL you tried? – Dharman May 10 '19 at 22:53
  • 1
    `SELECT dateColumn SUM(oneColumn) FROM table GROUP BY dateColumn` would do the trick. Just bind the resulting sum and the date – Qirel May 10 '19 at 22:53
  • ok, and the BIND_result can i use someting like $date, $total? – user21312321 May 10 '19 at 22:55
  • [How to enable MySQLi exception mode?](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments/22662582#22662582) – Dharman May 10 '19 at 22:59
  • check my result up. – user21312321 May 10 '19 at 22:59
  • 1
    @Qirel forgot one comma `,`, it should be `budget_date, SUM(budget_price)` – Dharman May 10 '19 at 23:00
  • ok, right its working, but.. is not make as GROUP by DATE – user21312321 May 10 '19 at 23:02
  • Try maybe `GROUP BY DATE(budget_date)`. What is your data type? – Dharman May 10 '19 at 23:05
  • its DATETIME.... – user21312321 May 10 '19 at 23:10
  • that shows a boolean error – user21312321 May 10 '19 at 23:12
  • What is your DB? MySQL? What boolean error? Did you enable the exception mode like I asked? – Dharman May 10 '19 at 23:13
  • Fatal error: Uncaught mysqli_sql_exception: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'drss_db_sales.public_budgets.budget_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /var/www/html/asp/inc/csv_excel_statistics_sales.php:51 Stack trace: #0 /var/www/html/asp/inc/csv_excel_statistics_sales.php(51): mysqli->prepare('SELECT budget_d...') #1 {main} thrown in /var/www/html/asp/inc/csv_excel_statistics_sales.php on line 51 – user21312321 May 10 '19 at 23:14
  • Change it to `SELECT DATE(budget_date) AS budget_date` – Barmar May 10 '19 at 23:15
  • Your SQL contains a typo, namely a missing comma in the `SELECT` clause: `SELECT budget_date, SUM(budget_price)`, etc. This causes `prepare()` to fail (and is a good reason to check the errors at every step. *Edit:* I reopened the question solely because it's not clear which DB you're using and thus not clear which duplicate is most appropriate. In any case, this should be put on hold as a typo or duplicate once you clarify the question. – elixenide May 10 '19 at 23:21
  • yup worked! ty.... – user21312321 May 10 '19 at 23:28
  • yup, what worked? Mind sharing with others who will face similar problem in future? – Dharman May 10 '19 at 23:29

1 Answers1

0

To create a SELECT statement with GROUP BY and SUM you can use this:

SELECT budget_date, SUM(budget_price) FROM public_budgets GROUP BY budget_date

However since your column data type is DATETIME it will contain also the time part and it will use it when grouping. If you want to group only with the DATE part you should use date() function in MySQL. 

SELECT budget_date, SUM(budget_price) FROM public_budgets GROUP BY DATE(budget_date)

However because you have switched on sql_mode=only_full_group_by you can only use the aggregate columns in SELECT, so your query should look something similar to this:

SELECT DATE(budget_date) AS budget_date, SUM(budget_price) FROM public_budgets GROUP BY DATE(budget_date)
Dharman
  • 30,962
  • 25
  • 85
  • 135