How to alias each resulting row of UNION in MySQL?

Question

How to alias each resulting row of UNION in MySQL?

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (1 votes)

1

Having the following table:

Productos
_______________
id_producto
nombre_producto
tipo_especial
fecha_inclusion

I would like to obtain the product account conditioning whether or not it is a type special, by aliasing each resulting row, in order to later to be able to work separately the results avoiding making a query for each special_ type.

SELECT COUNT(*), nombre_producto, tipo_especial
FROM productos
WHERE tipo_especial = 1
AND fecha_inclusion "2018-03-12"

UNION 
SELECT COUNT(*), nombre_producto
FROM productos
WHERE tipo_especial = 2
AND fecha_inclusion "2018-03-12"

Expected result:

                         | COUNT(*) | NOMBRE_PRODUCTO
producto_tipo_especial_1 |    1     | queso         
producto_tipo_especial_2 |    5     | jamon

Result obtained

| COUNT(*) | NOMBRE_PRODUCTO
|    1     | queso
|    5     | jamon

mysql

asked by Victor Alvarado 12.03.2018 в 15:14

source

3 answers

2

in MySQL, the keyword AS is prepended

SELECT COUNT(*) AS Cantidad, nombre_producto AS 'Nombre del Producto', tipo_especial AS 'Tipo Especial'
FROM productos
WHERE tipo_especial = 1
AND fecha_inclusion "2018-03-12"

If the word you want to use as alias is one you can put it directly, if there are several you should do it with quotes

answered by 12.03.2018 в 15:38

2

Greetings, the only thing you have to do is use AS and in quotes the name you want that column has

SELECT COUNT(*), nombre_producto AS 'Nombre del Producto', tipo_especial AS 'Tipo Especial'
FROM productos
WHERE tipo_especial = 1
AND fecha_inclusion "2018-03-12"

UNION 
SELECT COUNT(*), nombre_producto AS 'Nombre del Producto'
FROM productos
WHERE tipo_especial = 2
AND fecha_inclusion "2018-03-12"

answered by 12.03.2018 в 15:48

Change class css dynamically in javascript Hide div if you do not have multiple classes

score 1 · Accepted Answer

First and foremost, ALIAS in database is used for the names of the fields. Therefore, when talking about ALIAS, you are saying that you will change the names in the fields of a query, which is usually done by adding AS nombrecampo .

Your problem is that you really want to unequivocally identify the rows that each select of your union returns.

For that, you should add within your query a fixed string that will be repeated for each row of the select.

SELECT  "producto_tipo_especial_1" as descripcion, COUNT(*), nombre_producto, tipo_especial
FROM productos
WHERE tipo_especial = 1
AND fecha_inclusion "2018-03-12"

UNION 
SELECT "producto_tipo_especial_2" as descripcion, COUNT(*), nombre_producto
FROM productos
WHERE tipo_especial = 2
AND fecha_inclusion "2018-03-12"

Notice that in the fixed chain that I put, if I add an ALIAS. The fields have aliases, the rows have data ...