Concatenate two columns and add their quantities mysql

Question

Concatenate two columns and add their quantities mysql

Navigation

#1 by (0 votes)

0

'function table_format_formation ($ ven_id) {

$sql = "SELECT SUM(vd.tb_ventadetalle_can) as cantidad,cg.tb_categoria_nom, m.tb_marca_nom,GROUP_CONCAT(DISTINCT pr.tb_presentacion_nom) AS talla,vd.tb_ventadetalle_preunilin, (SUM(vd.tb_ventadetalle_can) * vd.tb_ventadetalle_preunilin) as subtotal FROM tb_venta v
  LEFT JOIN tb_cliente c ON v.tb_cliente_id=c.tb_cliente_id
  INNER JOIN tb_documento d ON v.tb_documento_id=d.tb_documento_id
  INNER JOIN tb_puntoventa pv ON v.tb_puntoventa_id=pv.tb_puntoventa_id
  INNER JOIN tb_ventadetalle vd ON v.tb_venta_id = vd.tb_venta_id
  LEFT JOIN tb_catalogo ct ON vd.tb_catalogo_id = ct.tb_catalogo_id
  LEFT JOIN tb_presentacion pr ON ct.tb_presentacion_id = pr.tb_presentacion_id
  LEFT JOIN tb_producto p ON pr.tb_producto_id = p.tb_producto_id
  LEFT JOIN tb_marca m ON p.tb_marca_id = m.tb_marca_id
  LEFT JOIN tb_categoria cg ON p.tb_categoria_id = cg.tb_categoria_id
  LEFT JOIN tb_unidad un ON ct.tb_unidad_id_bas = un.tb_unidad_id 
  LEFT JOIN tb_servicio s ON vd.tb_servicio_id = s.tb_servicio_id
  where v.tb_venta_id =$ven_id group by cg.tb_categoria_nom,vd.tb_ventadetalle_preunilin,pr.tb_presentacion_nom";

$oCado = new Cado();
$rst=$oCado->ejecute_sql($sql);
return $rst;

} '

As I would do it within the function group_concat, since I want to put it in my function a bit complex, I hope you can help me, thank you very much in advance

mysql php

asked by jantoni 02.03.2017 в 17:13

source

1 answer

Divisions in consultation to table Error: Crystal Reports - "errorKind"

score 0 · Accepted Answer

To do this, you have to separate the SQL in 2 stages:

Group by id, name to get the sum of values for each combination

Then you can group only by id using group_concat to get the desired results.

To separate the logic in those 2 stages, you need to use a derived table. This would be the query:

select id, group_concat(concat(name, ' (', sum_value, ')') order by name separator ', ')
  from (select id, name, sum(value) as sum_value
          from tbl
         group by id, name) t
 group by id
 order by id;

Demo