Inner Join Sub consultation

Question

Inner Join Sub consultation

Navigation

#1 by (1 votes)

0

I am making a query which should return the client's code, name, years and the quantity sold. When I do not perform the filter (which shows me repeated results) I have no problem this is the query:

select c.codigo_cliente, c.nombre, (to_number(to_char(sysdate,'YYYY')) - to_number(to_char(fecha_nacimiento,'YYYY'))) as edad, sum(precio*cantidad)
from clientes c inner join facturacion f on c.CODIGO_CLIENTE = f.CODIGO_CLIENTE
inner join detalle d on f.NUMERO = d.NUMERO;

but when I try not to show the repeated results (group them) I use the following query which gives me the error of invalid identifier in the field AMOUNT, the query I use and shows me the error of the identifier is the following:

select c.codigo_cliente, c.nombre, (to_number(to_char(sysdate,'YYYY')) - to_number(to_char(fecha_nacimiento,'YYYY'))) as edad, sum(precio*cantidad)
from clientes c inner join facturacion f on c.CODIGO_CLIENTE = f.CODIGO_CLIENTE
inner join (select codigo_producto,numero, sum(precio * cantidad) from detalle group by codigo_producto) d on f.numero = d.numero;

I'm working on an Oracle database

I attach the logical diagram of the database:

I thank you for your help.

sql oracle

asked by GersonRod_ 04.05.2018 в 09:03

source

1 answer

Shell Script: making this exit [closed] Is it possible to convert an Object result into an Arrangement in C #?

score 1 · Accepted Answer

I have changed the "structure" of the query simply for convenience.

This is how you have it:

SELECT C.CODIGO_CLIENTE, C.NOMBRE, (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) -  
TO_NUMBER(TO_CHAR(FECHA_NACIMIENTO,'YYYY'))) AS EDAD, SUM(PRECIO*CANTIDAD)--Error
FROM CLIENTES C 
INNER JOIN FACTURACION F ON C.CODIGO_CLIENTE = F.CODIGO_CLIENTE
INNER JOIN (SELECT CODIGO_PRODUCTO,NUMERO, SUM(PRECIO * CANTIDAD) 
            FROM DETALLE GROUP BY CODIGO_PRODUCTO) D ON F.NUMERO = D.NUMERO;

In the subquery you should give "aliases" to the columns with SUM() . How should the Subquery be, to work with the query you already have:

SELECT CODIGO_PRODUCTO,NUMERO, SUM(PRECIO * CANTIDAD) AS CANTIDAD--Te faltaba el alias
            FROM DETALLE GROUP BY CODIGO_PRODUCTO, NUMERO

Also something that I think is a good way to work, since you put "alias" to the tables, Customers = C, Billing = F ... Use them , both to avoid possible errors of ambiguous fields as for visual comfort when identifying fields