CODEWITHSUNDEEP
sqloracle-database
Matheus Souza
Matheus Souza

Reputation: 127

SQL - Get Month Name and SUM() of every month

I have the following task to perform on a table:

and I have a diagram for my current tables Tables

I'm sorry that it's in a foreign language but I'll translate.

I've tried joining the tables and then trying to grab the month by name but failed miserably, any help is appreciated!

I'm assuming I'll have to GROUP BY the SELECT result from the orders date?

This is Oracle SQL

Tried query

SELECT Clientte.nm_cliente, Item_pedido.nr_quantidade "Quantidade", Pedido.dt_venda 
FROM Clientte INNER JOIN Pedido
ON Clientte.id_cliente = Pedido.id_cliente
INNER JOIN Item_pedido
ON Pedido.id_pedido = item_pedido.id_pedido
INNER JOIN Produto
ON item_pedido.id_produto= Produto.id_Produto;

Upvotes: 0

Views: 111

Answers (2)

kfinity
kfinity

Reputation: 9091

I think you might want something like this.

SELECT to_char(Pedido.dt_venda, 'Month') as mon, 
    Clientte.nm_cliente, 
    count(distinct id_pedido) as num_orders,
    sum(Item_pedido.nr_quantidade * produto.nr_preco) total_value 
FROM Clientte 
INNER JOIN Pedido
  ON Clientte.id_cliente = Pedido.id_cliente
INNER JOIN Item_pedido
  ON Pedido.id_pedido = item_pedido.id_pedido
INNER JOIN Produto
  ON item_pedido.id_produto= Produto.id_Produto
GROUP BY to_char(Pedido.dt_venda, 'Month');

Two assumptions - you say you want "the quantity of orders", so I'm assuming you mean the number of unique ID_PEDIDOs for that month. You might instead want sum(nr_quantidade), the total number of items ordered, I'm not sure.

Second, you want "the total value of order", which I'm assuming means the total item quantity * price for all items in each order.

Upvotes: 1

Gratus D.
Gratus D.

Reputation: 877

SELECT Count(*), ID_PEDIDO, EXTRACT(month FROM date) FROM Pedido 
GROUP BY ID_PEDIDO, EXTRACT(month FROM date)

Can you see if this gets you closer?

Upvotes: 0

Related Questions