Transform database columns to rows and sum

Question

I'm building a system where I have to find the combined price of a computer system by using the database data. The first screenshot is a build from the system table.

Systems Table

Parts Table

The different kinds are: motherboard, case, ram, cpu, graphic. What I need is some way of turning the columns into rows and thereby summing the prices of each system.

Here is the table and content.

CREATE TABLE Component ( 
        nome VARCHAR(30), 
        kind VARCHAR(10), /*cpu, ram, mainboard, cases*/
        price INT,
        PRIMARY KEY(nome)
    );

CREATE TABLE Computer_system ( 
        nome VARCHAR(30),
        ram VARCHAR(20),
        cpu VARCHAR(20),
        mainboard VARCHAR(20),
        cases VARCHAR(20),
        gfx VARCHAR(20),
        PRIMARY KEY(nome)
    );


INSERT INTO Computer_system VALUES('SERVER1','D31','XEON1','LGA2011_D3_E_OGFX','CASE_A',null); 
INSERT INTO Computer_system VALUES('SERVER2','D43','XEON3','LGA2011_D4_E_OGFX','CASE_A',null);
INSERT INTO Computer_system VALUES('CONSUMER1','D43','I71','LGA1150_D4_ATX_OGFX','CASE_B',null); 
INSERT INTO Computer_system VALUES('GAMING1', 'D51', 'FX','AM3+_D5_ATX','BLACK_PEARL', 'NVIDIA_TITAN_BLACK_X');
INSERT INTO Computer_system VALUES('BUDGETO', 'D31', 'XEON1','LGA2011_D3_ATX','CASE_B', null);

Answer 1

There's a neat trick for unpivot in Postgres using UNNEST( ARRAY( ...) )

This efficiently (in one pass of the table) unpivots those multiple columns of table computer_system into multiple rows of (in this case) 3 columns: "nome", "colkind" and "colnome". An example of the unpivoted data:

|      nome |   colkind |              colnome |
|-----------|-----------|----------------------|
|   BUDGETO |       ram |                  D31 |
|   BUDGETO |       gfx |               (null) |
|   BUDGETO |     cases |               CASE_B |
|   BUDGETO | mainboard |       LGA2011_D3_ATX |
|   BUDGETO |       cpu |                XEON1 |

Once that data is available in that format it is simple to join to the Components table, like this:

SELECT
      *
FROM (
      /* this "unpivots" the source data */
      SELECT
           nome
         , unnest(array[ram, cpu, mainboard,cases,gfx]) AS colnome
         , unnest(array['ram', 'cpu', 'mainboard','cases','gfx']) AS colkind
      FROM Computer_system
      ) unpiv
INNER JOIN Components c ON unpiv.colnome = c.nome AND unpiv.colkind = c.kind
;

From here it is simple to arrive at this result:

|      nome | sum_price |
|-----------|-----------|
|   BUDGETO |       291 |
|   GAMING1 |       515 |
| CONSUMER1 |       292 |
|   SERVER1 |       285 |
|   SERVER2 |       289 |

using:

SELECT
      unpiv.nome, sum(c.price) sum_price
FROM (
      /* this "unpivots" the source data */
      SELECT
           nome
         , unnest(array[ram, cpu, mainboard,cases,gfx]) AS colnome
         , unnest(array['ram', 'cpu', 'mainboard','cases','gfx']) AS colkind
      FROM Computer_system
      ) unpiv
INNER JOIN Components c ON unpiv.colnome = c.nome AND unpiv.colkind = c.kind
GROUP BY
      unpiv.nome
;

See this SQLfiddle demo & please take note of the execution plan

QUERY PLAN
HashAggregate (cost=487.00..488.00 rows=100 width=82)
-> Hash Join (cost=23.50..486.50 rows=100 width=82)
Hash Cond: ((((unnest(ARRAY[computer_system.ram, computer_system.cpu, computer_system.mainboard, computer_system.cases, computer_system.gfx])))::text = (c.nome)::text) AND ((unnest('{ram,cpu,mainboard,cases,gfx}'::text[])) = (c.kind)::text))
-> Seq Scan on computer_system (cost=0.00..112.00 rows=20000 width=368)
-> Hash (cost=15.40..15.40 rows=540 width=120)
-> Seq Scan on components c (cost=0.00..15.40 rows=540 width=120)

Answer 2

I Think you need break down your table design into 3 table, there are Component, Computer_System and Computer_component. Below are the field list: Computer_System -> computer_id and name Component -> nome_component, kind, price Computer_Component -> computer_id, nome_component. With that table, you can sum the total price for each computer_id by join the Computer_System a JOIN Computer Component b ON a.computer_id = b.Computer id JOIN Component c ON b.nome_component = c.nome_component

Answer 3

you can do it simply with joining Computer_system table with Component for each kind like below query:

select c.nome as name,
  (coalesce(ram.price,0)
   +coalesce(cpu.price,0)
   +coalesce(+mainboard.price,0)
   +coalesce(cases.price,0)
   +coalesce(gfx.price,0)) as price
from Computer_system c
left join Components ram on c.ram=ram.nome
left join Components cpu on c.cpu=cpu.nome
left join Components mainboard on c.mainboard=mainboard.nome
left join Components cases on c.cases=cases.nome
left join Components gfx on c.gfx=gfx.nome

SQLFIDDLE DEMO

Answer 4

This is tricky to do because your table structures aren't suited to this type of query. Also, it is not flexible in case you want more than one gfx in a build.

Here is my suggested answer:

select sum(price)
from components
where nome in (
  select ram from computer_system where nome = 'GAMING1'
  UNION ALL 
  select cpu from computer_system where nome = 'GAMING1'
  UNION ALL 
  select mainboard from computer_system where nome = 'GAMING1'
  UNION ALL 
  select cases from computer_system where nome = 'GAMING1'
  UNION ALL 
  select gfx from computer_system where nome = 'GAMING1'
  )
;

And here it is in a working fiddle: http://sqlfiddle.com/#!15/228d7/8

If I restructured the tables, I would make them something like in this fiddle: http://sqlfiddle.com/#!15/f4ed06/1 with and extra parts_list table:

CREATE TABLE parts_list (
  system_nome VARCHAR(30),
  component_kind VARCHAR(10),
  component_nome VARCHAR(30),
  PRIMARY KEY (system_nome, component_kind, component_nome)
  );

and your query for the cost of the GAMING1 system becomes much simpler:

select sum(price)
from components as c 
inner join parts_list as PL ON c.kind = pl.component_kind and c.nome = pl.component_nome
where pl.system_nome = 'GAMING1'
;