Help with Oracle SQL query 11g

0

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:

    
asked by Fabian Montoya 20.04.2017 в 03:49
source

1 answer

0

You can try doing the counter inside the from, and then send it to call for each record.

SELECT ID_CLIENTE, COUNTER FROM 
(SELECT CI.ID_CLIENTE, COUNT(FA.ID_FACTURA) AS COUNTER
FROM CLIENTE CI 
INNER JOIN FACTURA FA ON FA.ID_CLIENTE = CI.ID_CLIENTE
GROUP BY FA.ID_FACTURA, CI.ID_CLIENTE);

You can add the additional details required by the request.

    
answered by 04.05.2017 в 19:40