Tengo dos tablas en una base de datos de Postgres:
mesa un
transaction_id | city | store_name | amount ------------------------------- 123 | London | McDonalds | 6.20 999 | NULL | KFC | 8.40 etc...mesa b
transaction_id | location | store_name | amount ----------------------------------- 123 | NULL | McDonalds | 6.20 999 | Sydney | KFC | 7.60 etc... Como puede ver, la ubicación puede faltar en una tabla pero estar presente en otra tabla. Por ejemplo, con la transacción 123, la ubicación está presente en la tabla a pero falta en la tabla b. Aparte de eso, el resto de los datos ( amount , store_name , etc.) son los mismos, fila por fila, suponiendo que nos unimos en la transaction_id .
Para un comerciante determinado, necesito recuperar una lista de ubicaciones y el monto total de esa ubicación.
Un ejemplo del resultado deseado:
KFC sales Report: suburb | suburb_total --------------- London | 2500 Sydney | 3500Lo que probé:
select coalesce(a.city, b.location) as suburb, sum(a.amount) as suburbTotal from tablea a join tableb b on a.transaction_id = b.transaction_id where a.store_name ilike 'KFC' group by(suburb); Pero aparece la column "a.city" must appear in the GROUP BY clause or be used in an aggregate function
Así que probé:
select coalesce(a.city, b.location) as suburb, sum(a.amount) as suburbTotal, max(a.city) as city_max, max(b.location) as location_max from tablea a join tableb b on a.transaction_id = b.transaction_id where a.store_name ilike 'McDonalds' group by(suburb);Pero, sorprendentemente, recibo el mismo error, aunque ahora estoy usando esa columna en una función agregada.
¿Cómo podría lograr el resultado deseado?
NB, hay razones por las que hemos desnormalizado los datos en dos tablas, que actualmente están fuera de mi control. Tengo que lidiar con eso.
EDITAR: agregado DESDE y UNIRSE, lo siento, olvidé escribirlos ...
A sus consultas les falta una cláusula from , lo que hace que no quede claro qué lógica está tratando de implementar.
Según sus datos de muestra y los resultados esperados, creo que es una full join en la transaction_id y luego la agregación. El uso de un parámetro posicional en la cláusula group by evita repetir la expresión:
select store_name, coalesce(a.city, b.location) as suburb, sum(amount) suburb_total from tablea a full join tableb b using(transaction_id) group by 1, 2Solo puedo imaginar recibir ese error con su consulta si el suburb fuera una columna en una de las tablas. Una forma de evitar esto es definir el valor en la cláusula from :
select v.suburb, sum(a.amount) as suburbTotal, max(a.city) as city_max, max(b.location) as location_max from tablea a join tableb b on a.transaction_id = b.transaction_id cross join lateral (values (coalesce(a.city, b.location))) as v(suburb) where a.store_name ilike 'McDonalds' group by v.suburb; Esta es una de las desventajas de permitir alias de columna en el group by . A veces, puede tener conflictos con las columnas de la tabla.