I'm having some problems with GROUP BY

Question

I'm having some problems with GROUP BY

Navigation

#1 by (1 votes)

0

I'm having a hard time putting together a select with 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 all the nozzles regardless of whether or not they have water in the day. If I do not have a supply, I want the nozzle to appear, but it will be zeroed, in my select the nozzle 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;

sql join postgresql group-by

asked by anonymous 18.05.2018 / 21:20

1 answer

Angular application does not run locally in IE? Limit tags in bootstrap-tagsinput v0.8.0

score 1 · Accepted Answer

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 );

Listing Abastecimentos in Bico with identifier 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 );

Listing Abastecimentos in Bico with identifier 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 );

Note 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 );

Output:

| 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 Abastecimentos in each of 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;

Output:

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

SQLFiddle: link