I’m having some problems with GROUP BY

Question

I’m having trouble assembling a select with the GROUP BY. What I want to do is this:

I have three tables:

bicos                           
    idbico, idempresa, idbomba 

abastecimentos
    idabastec, idbico, idempresa, valorabastecim

bomba
    idbomba, idempresa

I want to bring in every gig whether they have a supply or not on the day. In case I don’t have a supply, I want the beak to appear, but come zeroed, in my select the beak only comes with supply.

SELECT p.number
      ,h.number
      ,p.physicalnumber
      ,t.number AS tanknumber
      ,g.name AS fuelname
      ,SUM(CASE
             WHEN d.type = 6 THEN
              volume
             ELSE
              0
           END) AS testdelivery
      ,FIRST(oldvolumeetot ORDER BY completeddate) AS startvolumeetot
      ,LAST(newvolumeetot ORDER BY completeddate) AS endvolumeetot
      ,SUM(CASE
             WHEN d.type NOT IN (6) THEN
              volume
             ELSE
              0
           END) AS volume
      ,SUM(CASE
             WHEN d.type NOT IN (6) THEN
              VALUE
             ELSE
              0
           END) AS VALUE

  FROM hoses h
  JOIN pumps AS p
    ON h.stationid = p.stationid
   AND h.pumpid = p.pumpid
   AND h.number = p.number
  JOIN tanks AS t
    ON h.stationid = t.stationid
   AND h.tankid = t.tankid
  JOIN grades AS g
    ON t.stationid = g.stationid
   AND t.gradeid = g.gradeid
  LEFT JOIN deliveries AS d
    ON h.stationid = d.stationid
   AND h.hoseid = d.hoseid
   AND h.tankid = d.tankid
 WHERE h.stationid = 130
   AND completeddate >= '2018/05/17'
 GROUP BY h.stationid
         ,p.number
         ,h.number
         ,p.physicalnumber
         ,t.number
         ,g.name

 ORDER BY p.physicalnumber;

Answer 1

Assuming your data structure is something like:

CREATE TABLE tb_bico
(
  id BIGINT PRIMARY KEY,
  id_empresa BIGINT,
  id_bomba BIGINT
);


CREATE TABLE tb_abastecimento
(
  id BIGINT PRIMARY KEY,
  id_bico BIGINT,
  id_empresa BIGINT,
  valor NUMERIC(10,2),
  FOREIGN KEY (id_bico) REFERENCES tb_bico(id)
);

Registering Bicos:

INSERT INTO tb_bico ( id ) VALUES ( 1 );
INSERT INTO tb_bico ( id ) VALUES ( 2 );
INSERT INTO tb_bico ( id ) VALUES ( 3 );
INSERT INTO tb_bico ( id ) VALUES ( 4 );

Registering Abastecimentos in the Bico identifiable 1:

INSERT INTO tb_abastecimento ( id, id_bico, valor  ) VALUES ( 100, 1, 120.50 );
INSERT INTO tb_abastecimento ( id, id_bico, valor  ) VALUES ( 200, 1, 420.90 );

Registering Abastecimentos in the Bico identifiable 2:

INSERT INTO tb_abastecimento ( id, id_bico, valor  ) VALUES ( 300, 2, 20.35 );
INSERT INTO tb_abastecimento ( id, id_bico, valor  ) VALUES ( 400, 2, 10.50 );
INSERT INTO tb_abastecimento ( id, id_bico, valor  ) VALUES ( 500, 2, 22.10 );
INSERT INTO tb_abastecimento ( id, id_bico, valor  ) VALUES ( 600, 2, 12.45 );

Notice that the Bicos with the identifiers 3 and 4 do not have Abastecimentos.

Solution:

SELECT
  bc.id AS id_bico,
  ab.id AS id_abastecimento,
  COALESCE( ab.valor, 0.0 ) AS valor
FROM
  tb_bico AS bc
LEFT JOIN
  tb_abastecimento AS ab ON ( bc.id = ab.id_bico );

Exit:

| id_bico | id_abastecimento | valor |
|---------|------------------|-------|
|       1 |              100 | 120.5 |
|       1 |              200 | 420.9 |
|       2 |              300 | 20.35 |
|       2 |              400 |  10.5 |
|       2 |              500 |  22.1 |
|       2 |              600 | 12.45 |
|       4 |           (null) |     0 |
|       3 |           (null) |     0 |

If you want a query to return the total value of all the Abastecimentos in each of the Bicos:

SELECT
  bc.id AS id_bico,
  SUM( COALESCE( ab.valor, 0.0 ) ) AS valor_total
FROM
  tb_bico AS bc
LEFT JOIN
  tb_abastecimento AS ab ON ( bc.id = ab.id_bico )
GROUP BY
  bc.id;

Exit:

| id_bico | valor_total |
|---------|-------------|
|       2 |        65.4 |
|       4 |           0 |
|       1 |       541.4 |
|       3 |           0 |

Sqlfiddle: http://sqlfiddle.com/#! 17/54a328/5