CODEWITHSUNDEEP
mysqlsql
Aleksandr Savkin
Aleksandr Savkin

Reputation: 403

Get name, date and sum from orders in March

I have this query:

SELECT * 
FROM ((orders_books 
JOIN books ON orders_books.id_book = books.id) 
JOIN orders ON orders_books.id_order=orders.id) 
JOIN users ON user_id=users.id 
WHERE date >='2019-03-1' AND date <= '2019-03-31';

Query result: https://image.prntscr.com/image/s3ngzzYrSd27mFib9HbLmA.png

I find number of orders:

SELECT name, date, SUM(book_price) 
FROM ((orders_books 
INNER JOIN books ON orders_books.id_book = books.id) 
INNER JOIN orders ON orders_books.id_order=orders.id) 
INNER JOIN users ON user_id=users.id 
WHERE date >='2019-03-1' AND date <= '2019-03-31';

[Amount for all orders] : https://image.prntscr.com/image/o88Hf4uKSHGuZ2ESUECzuA.png

But I need to find the order amount separately for each order.

Upvotes: 0

Views: 38

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

If you need the sum for order.id then use group by orders.id

    SELECT orders.id  SUM(book_price) 
    FROM orders_books 
    INNER JOIN books ON orders_books.id_book = books.id
    INNER JOIN orders ON orders_books.id_order=orders.id

    WHERE date >='2019-03-1' AND date <= '2019-03-31'
    group by orders.id

Upvotes: 0

dargin
dargin

Reputation: 61

SELECT orders.id, SUM(book_price) 
FROM ((orders_books 
INNER JOIN books ON orders_books.id_book = books.id) 
INNER JOIN orders ON orders_books.id_order=orders.id) 
INNER JOIN users ON user_id=users.id 
WHERE date >='2019-03-1' AND date <= '2019-03-31'
GROUP BY order_id;

Upvotes: 0

Andronicus
Andronicus

Reputation: 26046

How about:

SELECT id_order, SUM(book_price) 
FROM ((orders_books 
INNER JOIN books ON orders_books.id_book = books.id) 
INNER JOIN orders ON orders_books.id_order=orders.id) 
INNER JOIN users ON user_id=users.id 
WHERE date >='2019-03-1' AND date <= '2019-03-31'
GROUP BY id_order;

Upvotes: 1

Related Questions