How to count rows called orders from the last 30 days and sum up the price of that order? - Phpmyadmin

IanO39Loughlin
April 10, 2019
221 views
2 votes
3 Answers

The order table is made up of quantity, price, username and orderdate which is in datetime format.

An order is submitted from a cart page, if multiple items are submitted such as 8 monitors and 2 pcs. It will input those as two separate records.

The goal: To multiply the quantity by the price for each record a user has, then to add those records up for the last 30 days.

Tried the below code to get the orders from the last 30 days which isnt working.

edit: working code for getting orders from last 30 days

SELECT * FROM `orders` 
where orderdate between adddate(now(),-30) and now()

Honestly not sure where to start with the above sql statement, not sure where exactly the groupbys, count and sum() functions go since I am only starting with sql.

Answers

You can use below query

SELECT SUM(`price`) AS sum FROM orders
WHERE `orderdate` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

If you need to count row and sum the sales price

SELECT SUM(`price`) AS sum,COUNT(*) AS counter FROM orders
WHERE `orderdate` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

Alternatively, if you wanted these queries by username,

SELECT SUM(`price`) AS sum FROM orders
WHERE uname = 'test' and `orderdate` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

SELECT SUM(`price`) AS sum,COUNT(*) AS counter FROM orders
where uname = 'test' and `orderdate` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

- GordonLinoff
- April 10, 2019 at 1:43 pm
- 0 votes
0
Be more explicit:
```
WHERE orderdate >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
```
Or, because you probably do not care about the time component:
```
WHERE orderdate >= CURRENT_DATE - INTERVAL 30 DAY
```
Login or Signup to reply.

- MadhurBhaiya
- April 10, 2019 at 1:56 pm
- 0 votes
0
In order to get the “total order value” for the last 30 days, you need to calculate SUM() on price*quantity:
```
SELECT SUM(price*quantity) AS total_order_value 
FROM orders 
WHERE orderdate BETWEEN NOW() - INTERVAL 30 DAY and NOW()
```
Login or Signup to reply.