I need to make two queries in sql, but I'm stuck in one part, I explain.
The first query asks me the following:
You want to know by customer: The total of invoices made in the month, the total value of purchases made in the month, the value of the minimum invoice, the value of the maximum invoice and the average value of purchases made in the month; the query must show, customer ID, customer's name and surname, total customer invoices, the invoice with the least value, the invoice with the highest value, and finally the average of the total value of the invoices.
Now, I am involved in that the grouping is not doing it correctly, without that, I do not know how to do the verification of the invoice with the least value, the one with the highest nor the average of these invoices.
The query that I have made is the following:
SELECT CI.ID_CLIENTE, CONCAT(CONCAT(CI.CLI_NOMBRE, ' '), CI.CLI_APELLIDO) AS "NOMBRE CLIENTE",
COUNT(FA.ID_FACTURA) AS "CANTIDAD FACTURAS", SUM(DF.FAC_PRECIO_TOTAL)
FROM CLIENTE CI
INNER JOIN FACTURA FA ON FA.ID_CLIENTE = CI.ID_CLIENTE
INNER JOIN DETALLE_FACTURA DF ON DF.ID_FACTURA = FA.ID_FACTURA
group by CI.ID_CLIENTE, CONCAT(CONCAT(CI.CLI_NOMBRE, ' '), CI.CLI_APELLIDO), FA.ID_FACTURA
ORDER BY CI.ID_CLIENTE ASC;
But this one does the counting of the wrong invoices, I'll show you:
But that's wrong, in the table of bills, only the first two customers (1 and 2) have three bills, the others only have two:
And if I only group it by client, it shows me this way:
SELECT CI.ID_CLIENTE, CONCAT(CONCAT(CI.CLI_NOMBRE, ' '), CI.CLI_APELLIDO) AS "NOMBRE CLIENTE",
COUNT(FA.ID_FACTURA) AS "CANTIDAD FACTURAS"
FROM CLIENTE CI
INNER JOIN FACTURA FA ON FA.ID_CLIENTE = CI.ID_CLIENTE
INNER JOIN DETALLE_FACTURA DF ON DF.ID_FACTURA = FA.ID_FACTURA
group by CI.ID_CLIENTE, CONCAT(CONCAT(CI.CLI_NOMBRE, ' '), CI.CLI_APELLIDO)
ORDER BY CI.ID_CLIENTE ASC;
I do not know how to do it then, the truth is that I've been involved in this consultation for about an hour, which is sure to be dumb: /
The Entity-Relationship Model is the following: