CODEWITHSUNDEEP
sql
Snedden27
Snedden27

Reputation: 1920

Counting instances of an entry in one table from another table in SQL

I have two sql tables site and sms

The structure is ROUGHLY like

SENEGAL_SITE:

siteID   Lon        Lat
1        11.232      12.32
2        12.232      12.42
3        11.232      12.62
4        11.232      11.42

ATA_SMS_Apr:

  out_going_site_id  inSite  no_sms
         4            1         65
         2            4         21
         3            4         54

i want to query out a result somthing like this

site id     SMS_Site_count
1               5
2               3
3               1

So basically I want to count the number of sms through each site tower

The query which I used to do this is this

select *  ,
count((select * 
       from ATA_SMS_Apr a 
       where s.site_id=a.out_going_site_id)) 
from SENEGAL_SITE s

Doing this I get a error as Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Upvotes: 1

Views: 32

Answers (1)

radar
radar

Reputation: 13425

you need a correlated subquery to get the sms count from ATA_SMS_Apr table for each site id

The count is moved inside the subquery from outside.

select * ,
(select count(*) 
 from ATA_SMS_Apr a 
 where s.site_id=a.out_going_site_id) as SMS_Site_Count
from SENEGAL_SITE s

Upvotes: 2

Related Questions