CODEWITHSUNDEEP
mysql
Riri
Riri

Reputation: 25

How to sum all the value in a specific date in mysql

i want to sum all the values in a specific date in mysql but idk what is the right syntax

CREATE TABLE `trans` (
  `id` int(12) NOT NULL,
  `date_sold` datetime NOT NULL,
  `total` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `trans`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `trans`
  MODIFY `id` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
id date_sold total
1 2021-02-23 12
2 2021-02-23 6
3 2021-02-24 32
4 2021-02-24 10

now i want to sum all the values in that specific date

ex:

id date total
1 2021-02-23 18
2 2021-02-24 42

is that possible?

Upvotes: 0

Views: 1011

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3457

Alternative Use of ROW_NUMBER() function. Because ROW_NUMBER() isn't supported in below v5.8. First calculate date wise total and then apply id incrementally.

-- MySQL(5.6)
SELECT (@row_number:=  @row_number + 1) id
     , t.date_sold, t.total
FROM (SELECT date_sold
           , SUM(total) total
      FROM trans
      GROUP BY date_sold ) t, (SELECT @row_number := 0) x 
ORDER BY t.date_sold

Please check from URL https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=5cc2305b465ac2454be5bdb1a9e8af4a

Upvotes: 2

Related Questions