Both GROUP BY clauses in my SQL show an error

Question

Both of my GROUP BY clauses on lines Line 57 and 106 show an error.

SELECT
'' AS product_name,
'5' AS id_rfq,
spq_master.id_spq,
'' AS id_product,
forneced_ram.codigo AS id_supplier,
forneced_ram.designacao AS suppliers,
'' AS qty_in_units,
forneced_ram.cod_pais AS origin,
spq_detail.item_qty AS qty_available,
spq_detail.item_price AS price_fob,
spq_detail.air_freight AS air_freight,
(spq_detail.item_price * spq_detail.item_qty) + spq_detail.air_freight AS price_total,
spq_master.delivery_time AS deadline,
spq_detail.item_qty / COUNT(spq_detail.id_spq) AS perc,
spq_master.payments_terms AS payment_condition,
spq_master.validity AS validity_proposal,
spq_detail.approved_trading AS pick,
CASE WHEN spq_detail.item_price <= CAST(sub.min_price AS money) AND spq_master.delivery_time = sub.min_date THEN '#9ae6a6' ELSE '' END AS ux_linecolor,
CASE WHEN spq_detail.item_price <= CAST(sub.min_price AS money) AND spq_master.delivery_time = sub.min_date THEN '#000000' ELSE '' END AS ux_textcolor
FROM
(SELECT MIN(spq_detail.item_price) AS min_price,
MIN(spq_master.delivery_time) AS min_date
FROM spq_detail
INNER JOIN spq_master ON spq_master.id_spq = spq_detail.id_spq
WHERE spq_detail.id_product = ''
AND spq_master.id_rfq = 5
AND spq_master.company = 'S1'
AND spq_detail.company = 'S1') AS sub, spq_master
INNER JOIN spq_detail ON spq_master.id_spq = spq_detail.id_spq
INNER JOIN forneced_ram ON spq_master.id_supplier = forneced_ram.codigo
WHERE spq_master.company = 'S1'
AND spq_master.id_rfq = '5'
AND spq_detail.company = 'S1'
AND forneced_ram.empresa = 'S1'
AND spq_detail.id_product = ''
AND spq_master.validity > '20-apr-2020'
AND 1 = 2
GROUP BY product_name,
         id_rfq,
         spq_master.id_spq,
         id_product,
         id_supplier,
         suppliers,
         qty_in_units,
         origin,
         qty_available,
         price_fob,
         air_freight,
         price_total,
         deadline,
         spq_detail.item_qty,
         validity_proposal,
         pick,
         ux_linecolor,
         ux_textcolor
UNION ALL
SELECT
DISTINCT
artigos_ram.designacao AS product_name,
'5' AS id_rfq,
spq_detail.id_spq,
spq_detail.id_product AS id_product,
'' AS id_supplier,
'' AS qty_in_units,
'' AS suppliers,
'' AS origin,
spq_detail.item_qty AS qty_available,
spq_detail.item_price AS price_fob,
spq_detail.air_freight AS air_freight,
(spq_detail.item_price * spq_detail.item_qty) + spq_detail.air_freight AS price_total,
'' AS deadline,
spq_detail.item_qty / COUNT(spq_detail.id_spq) AS perc,
'' AS payment_condition,
spq_master.validity AS validity_proposal,
spq_detail.approved_trading AS pick,
'' AS ux_linecolor,
'' AS ux_textcolor
FROM spq_master
INNER JOIN spq_detail ON spq_master.id_spq = spq_detail.id_spq
INNER JOIN artigos_ram ON artigos_ram.codigo = spq_detail.id_product
WHERE spq_master.company = 'S1'
AND spq_master.id_rfq = 5
AND spq_detail.company = 'S1'
AND spq_master.id_spq = 5
AND 2 = 2
GROUP BY product_name,
         id_rfq,
         spq_detail.id_spq,
         id_product,
         id_supplier,
         qty_in_units,
         suppliers,
         origin,
         qty_available,
         price_fob,
         air_freight,
         price_total,
         deadline,
         spq_detail.item_qty,
         payment_condition,
         validity_proposal,
         pick,
         ux_linecolor,
         ux_textcolor

You cannot generally group by aliases. However in order for stack overflow users to help you, you need to provide more information. At least the database technology (SQLServer, Oracle, MySQL, etc), Table structure, sample data and expected results. https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Gabriel Durac, Apr 20 '20 at 21:59
https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by/3841804 — Gabriel Durac, Apr 20 '20 at 22:02

Both GROUP BY clauses in my SQL show an error

0 Answers0