how to make it a single line

Question

how to make it a single line

Navigation

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

3

I have this query

SELECT
  'presupuesto_annio_real'.'categoria',
  IF('clase_canal'.'canal' = 'constructor',sum('presupuesto_annio_real'.'cantidad'),0) as constructorCantidad,
  IF('clase_canal'.'canal' = 'distribuidor',sum('presupuesto_annio_real'.'cantidad'),0) as distribuidorCantidad,
  IF('clase_canal'.'canal' = 'salas',sum('presupuesto_annio_real'.'cantidad'),0) as salasCantidad
FROM
  'presupuesto_annio_real'
  INNER JOIN 'clase_canal' ON ('presupuesto_annio_real'.'clase' = 'clase_canal'.'clase')
  where 'presupuesto_annio_real'.'referencia' = '200142'
GROUP BY
  'presupuesto_annio_real'.'categoria',
  'clase_canal'.'canal'

and throws me the next result

----------------------------------------------------------------
categoria|contructorCantidad|distribuidorCantidad|SalasCantidad|
---------------------------------------------------------------
di       |200               | 0                  | 0
---------------------------------------------------------------
di       | 0                | 32                 | 0
---------------------------------------------------------------
di       | 0                | 0                  | 9
---------------------------------------------------------------

and I need the result to be as follows

----------------------------------------------------------------
categoria|contructorCantidad|distribuidorCantidad|SalasCantidad|
---------------------------------------------------------------
di       |200               | 32                 | 9           |
---------------------------------------------------------------

mysql sql

asked by Eduard 19.09.2018 в 16:58

source

2 answers

0

In your WHERE try adding that they are different from 0 so you will get the result you want without the zero as the following example:

where 'presupuesto_annio_real'.'referencia' = '200142' and constructorCantidad !=0 and NombredeColumna !=0

I hope I can help you

answered by 19.09.2018 в 17:32

error when uploading image with laravel? Retrieve a variable sent to server

score 3 · Accepted Answer

You can flip the condition and group only by category, like this:

SELECT
  'presupuesto_annio_real'.'categoria',
  sum(IF('clase_canal'.'canal' = 'constructor','presupuesto_annio_real'.'cantidad',0)) as constructorCantidad,
  sum(IF('clase_canal'.'canal' = 'distribuidor','presupuesto_annio_real'.'cantidad',0)) as distribuidorCantidad,
  sum(IF('clase_canal'.'canal' = 'salas','presupuesto_annio_real'.'cantidad',0)) as salasCantidad
FROM
  'presupuesto_annio_real'
  INNER JOIN 'clase_canal' ON ('presupuesto_annio_real'.'clase' = 'clase_canal'.'clase')
  where 'presupuesto_annio_real'.'referencia' = '200142'
GROUP BY
  'presupuesto_annio_real'.'categoria'

This should give you the expected result.