Need to join and sum on sql tables

Question

I am trying to learn Microsoft SQL Server and I'm trying to figure out how I can do the following.

I have two tables:

users

id  fname   lname   username
111 kely    dike    kdike
222 hana    sho     hsho
333 mike    dime    mdime
444 sergi   xan     sxan

and

email_recipient

id  recipient_id    fname   lname
1      222          hana    sho
2      222          hana    sho
3      222          hana    sho
4      333          mike    dime
5      111          kely    dike
6      111          kely    dike
7      444          sergi   xan
8      444          sergi   xan
9      444          sergi   xan
10     444          sergi   xan

and what I want to do is to count how many emails each user received, something like this:

id  fname   lname  username email_recipient total
111 kely    dike   kdike          2
222 hana    sho    hsho           3
333 mike    dime   mdime          1
444 sergi   xan    sxan           4

Could someone please point in the right direction?

Why microsoft sql? How about ANSI SQL?

Kermit
– Kermit

2013-01-08 22:01:46 +00:00
Commented Jan 8, 2013 at 22:01 — Kermit
– Kermit, Commented Jan 8, 2013 at 22:01

Kermit · Accepted Answer · 2013-01-08 22:04:41Z

3

You can try this.

SELECT a.id, a.fname, a.lname, a.username, COUNT(*) AS email_recipient_total
FROM users a
INNER JOIN email_recipient b ON b.recipient_id = a.id
GROUP BY a.id, a.fname, a.lname, a.username

Result

|  ID | FNAME | LNAME | USERNAME | EMAIL_RECIPIENT_TOTAL |
----------------------------------------------------------
| 111 |  kely |  dike |    kdike |                     2 |
| 222 |  hana |   sho |     hsho |                     3 |
| 333 |  mike |  dime |    mdime |                     1 |
| 444 | sergi |   xan |     sxan |                     4 |

See it in action

answered Jan 8, 2013 at 22:04

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bohemian Over a year ago

Why did you ask the question if you knew the answer?

Kermit Over a year ago

@Bohemian I asked why Microsoft SQL

exexzian Over a year ago

@njk Do we need to group them by all GROUP BY a.id, a.fname, a.lname, a.username ? I tested your fiddle and it works same for GROUP BY a.id

Kermit Over a year ago

@sansix The fiddle was tested on a MySQL environment. OP is using MSSQL which is ANSI standard.

exexzian Over a year ago

@njk so you mean to say "it may produce different result on MSSQL" if one goes with GROUP BY a.id only

|

sgeddes · Accepted Answer · 2013-01-08 22:42:49Z

0

My guess is this might have a slightly better performance, but nonetheless, essentially the same answer:

SELECT 
  a.id, 
  a.fname, 
  a.lname, 
  a.username, 
  b.email_recipient_total
FROM users a
  INNER JOIN (
      select recipient_id, COUNT(*) AS email_recipient_total
      from email_recipient
      group by recipient_id
    ) b ON b.recipient_id = a.id

answered Jan 8, 2013 at 22:42

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Collectives™ on Stack Overflow

Need to join and sum on sql tables

2 Answers 2

See it in action

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related