SQL query - need help

[Anon]

I have a table wich has the following columns:

• postcard_id
• postcard_sender_id
• postcard_recipient_id
• postcard_postal_id

And I want to get the total number of postcard table registers that each SENDER_ID sent to each RECIPIENT_ID

If table looked like:

postcard_id | sender_id | recipient_id
1 | 1 | 2
2 | 3 | 1
3 | 1 | 2

The returned result set would have

sender_id | recipient_id | number
1 | 2 | 2
3 | 1 | 1

And so on.

Thanks in advance.

[Anon]

Not tested , and not even sure if it is what you ask for, but try:

SELECT sender_id, receipent_id, count(receipent_id) as number FROM registers GROUPBY Sender_ID;

[Anon]

Kinda works, but If the table has several registers from SENDER to several DESTINATIONS, it only lists one per SENDER.

Any ideas?

thanks for helping

[Anon]

Table is like:

id | to | from
1 | 1 | 2

2 | 1 | 3

3 | 1 | 2

4 | 1 | 2

5 | 2 | 1

6 | 2 | 3

7 | 3 | 1

8 | 2 | 4

(from=sender)
(to=recipient)

And that SQL query shows:

From: 1
To: 2
Many: 2

From: 2
To: 1
Many: 3

From: 3
To: 1
Many: 2

From: 4
To: 2
Many: 1

It returns the "FROM", the FIRST "TO" it finds that belongs to that "FROM" and counts the total of occurencies of that "FROM".

Thanks for helping.

[[deleted]]

try grouping by sender and by destination:

SELECT sender_id, recipent_id, count(1) as number
FROM registers
GROUP BY Sender_ID, recipient_id;

Now you'll get the number of occurences of each unique sender/recipient combination.

[Anon]

Thanks for both your help.

It seams to be working now.

Thanks again.