Postgresql Database

Question

Postgresql Database

Navigation

#1 by (1 votes)

0

How do I make a select with sum and inner join to add a value x between 3 tables. A query return to the lowest possible number of results.

create table credit_card (
        credit_card_id int primary key,
    nome varchar (30)
);

create table store (
    store_id int primary key,
    nome varchar (30)
);


create table sale (
    id serial primary key,
    data date,
    valor numeric,
    credit_card_fk int references credit_card (id),
    store_id_fk int references store (id)
);

     select * from store
     select * from credit_card
     select * from sale
     DROP TABLE CREDIT_CARD CASCADE

I do not know how to do it:

   select  store.nome, store_id_fk, credit_card.nome, credit_card_fk, sale.valor, sale.data
   from sale 
   inner join store on (store.id= sale.store_id_fk)
   inner join credit_card on (credit_card.id= sale.credit_card_fk)
   order by store

postgresql inner sum

asked by anonymous 28.01.2018 / 10:34

1 answer

Return function true returns false on a Vue expression Check if foreign key is being used

score 1 · Accepted Answer

Only one table has value to be added, so it does not have that question of "Adding X value between 3 tables". You must do the inner join only to fetch the data from the credit_card and store tables, the value you will add normally. The question is: which columns do you have to group?

As you said you should return as few results as possible, I believe you are grouping by card and store, showing the total sold for each one. I made an example, Here is the code:

select
    t.nome as store_nome,
    c.nome as card_nome,
    sum(s.valor) as total_sale
from sale s
inner join store t on t.store_id = s.store_id_fk
inner join credit_card c on c.credit_card_id = s.credit_card_fk
group by t.nome, c.nome

I put it in SQLFiddle: link