CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
Joan Triay
Joan Triay

Reputation: 1658

How to count by two fields and join with other table Postgres?

I have two tables, one table user and second table transactions related with the transactions done by a user. I want to do a query that give me the count by name and date, with the fields in user table. How can I do it?

Table user:

Name   Id Card
-----------------
Alex   01  N
James  02  Y


Table transaction:

Name   Date
-----------------
Alex   01/07/2012
Alex   01/12/2012
James  01/08/2012
Alex   01/07/2012
Alex   01/12/2012
James  01/07/2012
James  01/07/2012

I want sometihng like this:

Name   Date         Transactions    ID   Card
---------------------------------------------
Alex   01/07/2012   2               01   N
Alex   01/12/2012   2               01   N
James  01/08/2012   1               02   Y 
James  01/07/2012   2               02   Y

First of all I tryed to count by two columns with something like this:

select name, date, count(name, date) from pm_transaction GROUP BY (name,date)
select count(distinct(machine, date)) from pm_transaction

But it does not work, I tried a lot of combinations but no one works

Upvotes: 0

Views: 143

Answers (2)

Edgars T.
Edgars T.

Reputation: 1139

This looks like simple aggregation task. Just check and correct table join condition and table names:

select u.name, t.date, count(1) as transactions, u.id, u.card
from transaction t
join user_table u on u.name = t.name
group by u.name, t.date, u.id, u.card;

Upvotes: 0

skazied
skazied

Reputation: 46

Try this

select tb1.name, tb2.date , tb2.transaction , tb1.Id, tb1.card from tbUser as tb1 inner join (select date, name, count(date) as transaction from tbTransaction group by date) as tb2 on tb1.name = tb2.name

Upvotes: 1

Related Questions