Olá Fabiano
Avaliar se vale a pena ... (desempenho, custo e complexidade)
Levando em consideração os dados que vc passou, verificar se atende ...
/*
Preciso recuperar essas informações da seguinte forma:
ID | PNAME | ACT - A | WEIGHT | ACT - C | WEIGHT | ACT - B | WEIGHT
-----+---------------+-------------+--------------+-------------+---------------+------------+----------
3 | Bolt | 9.5 | 2 | 4.0 |
2 | 2.5 | 1
*/
/*
DROP TABLE IF EXISTS teste;
CREATE TABLE teste
(
id integer
, pname varchar(25)
, description varchar(50)
, note numeric(10,1)
, weight integer
);
INSERT INTO teste (id, pname, description, note, weight) VALUES ( 3
, 'Bolt' , 'ACT - A' , 4.5 , 2 );
INSERT INTO teste (id, pname, description, note, weight) VALUES ( 3
, 'Bolt' , 'ACT - A' , 5.0 , 2 );
INSERT INTO teste (id, pname, description, note, weight) VALUES ( 3
, 'Bolt' , 'ACT - C' , 3.0 , 2 );
INSERT INTO teste (id, pname, description, note, weight) VALUES ( 3
, 'Bolt' , 'ACT - C' , 1.0 , 2 );
INSERT INTO teste (id, pname, description, note, weight) VALUES ( 3
, 'Bolt' , 'ACT - B' , 2.0 , 1 );
INSERT INTO teste (id, pname, description, note, weight) VALUES ( 3
, 'Bolt' , 'ACT - B' , 0.5 , 1 );
*/
SELECT * FROM teste;
SELECT id
, pname
, sum("ACT - A") AS "ACT - A"
, sum(weight_a) weight
, sum("ACT - C") AS "ACT - C"
, sum(weight_c) weight
, sum("ACT - B") AS "ACT - B"
, sum(weight_b) weight
FROM
(
SELECT id
, pname
, "ACT - A"
, CASE WHEN "ACT - A" <> 0 THEN
weight
ELSE
0
END AS weight_a
, "ACT - C"
, CASE WHEN "ACT - C" <> 0 THEN
weight
ELSE
0
END AS weight_c
, "ACT - B"
, CASE WHEN "ACT - B" <> 0 THEN
weight
ELSE
0
END AS weight_b
FROM
(
SELECT id
, pname
, weight
, sum
(
CASE WHEN description = 'ACT - A' THEN
note
ELSE
0
END
) AS "ACT - A"
, sum
( CASE WHEN description = 'ACT - C' THEN
note
ELSE
0
END
) AS "ACT - C"
, sum
(
CASE WHEN description = 'ACT - B' THEN
note
ELSE
0
END
) AS "ACT - B"
FROM teste
GROUP BY id
, pname
, description
, weight
) AS x
) AS y
GROUP BY id
, pname
Espero ter ajudado,
Emerson Hermann
Em 15 de abril de 2011 10:57, fabiano.fernandes
<[email protected]> escreveu:
> Olá a todos!
>
> Estou precisando de um empurrão para criação de um select ...
>
> Alguem pode me ajudar?
>
> Tenho em uma tabela da seguinte forma:
>
>
> ID | PNAME | DESCRIPTION | NOTE | WEIGHT
> -----+---------------+-----------------------+----------+--------------
> 3 | Bolt | ACT - A | 4.5 | 2
> 3 | Bolt | ACT - A | 5.0 | 2
> 3 | Bolt | ACT - C | 3.0 | 2
> 3 | Bolt | ACT - C | 1.0 | 2
> 3 | Bolt | ACT - B | 2.0 | 1
> 3 | Bolt | ACT - B | 0.5 | 1
>
>
>
> Preciso recuperar essas informações da seguinte forma:
>
>
> ID | PNAME | ACT - A | WEIGHT | ACT - C | WEIGHT | ACT - B | WEIGHT
> -----+---------------+-------------+--------------+-------------+---------------+------------+----------
> 3 | Bolt | 9.5 | 2 | 4.0 | 2
> | 2.5 | 1
>
>
> Att
> Fabiano Fernandes
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Ajuda-com-select-tp4305614p4305614.html
> Sent from the PostgreSQL - Brasil mailing list archive at Nabble.com.
> _______________________________________________
> pgbr-geral mailing list
> [email protected]
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
