Postgresql Database

Asked

Viewed 49 times

0

How do I select with sum and inner join to sum an x value between 3 tables. A query to return as few results as possible.

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

That I don’t know how to do:

   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

  • You will have to make the 3 selects, unite them with UNION (search the command, it’s quite simple). After that, you will make a select in that group you created, using the SUM() to sum the values and the MIN() to see which is the smallest. If you can’t, post your bank to http://sqlfiddle.com/ so we can simulate.

1 answer

1


Only one table has value to be added, so there is no question of "Add value X between 3 tables". You should do the Inner Join only to fetch the data from the tables credit_card and store, the value you will normally add up. The question is: By which columns you have to group?

As you said you should return as few results as possible, I believe it is grouping by card and store, showing the total sold for each. I made an example, follow 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 in Sqlfiddle: http://sqlfiddle.com/#! 17/7fc5d/1

Recommended reading:

https://www.postgresql.org/docs/9.4/static/tutorial-agg.html

Browser other questions tagged postgresql inner sum

You are not signed in. Login or sign up in order to post.