SQL Count Matches

[mikesoper]

Hi all,
I have 2 tables:

Stats_table & output_table

In stats table there are 10k records, with a unique ID (inst_ID).

In output table there will be several thousand records of customers, and each customer also has an inst_ID assigned.

What I need to do is for every unique inst_ID in the stats table, count how many times this ID occurs in the output_table and display the total.

There is also an additional column in the output_table called cust_frequency. In the same query I also need to sum the cust_frequency total for each inst_id

So my results may look something like:

inst_ID countfromoutput_table sumOfFrequency
1234 50 500
2345 5 10
4555 20 100

I need to return all records from the stats_table regardless of whether or no there are any matches in the output_table

Hope that makes sense and hope someone can help.

Much appreciated

 

[r937]

this is a classic LEFT OUTER JOIN with GROUP BY, COUNT, and SUM

what was it you were having trouble with?

or have you never written a join query before?

(i'm asking because your level of skill will dictate the kinds of replies you would get)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mikesoper]

Hi there,

I am comfortable with basic join queries, and sums, but I have not used them in combination before

 

[r937]

SELECT stats_table.inst_ID
     , COUNT(output_table.inst_ID) AS countfromoutput_table
     , SUM(output_table.cust_frequency) AS sumOfFrequency
  FROM stats_table 
LEFT OUTER
  JOIN output_table
    ON output_table.inst_ID = stats_table.inst_ID
GROUP
    BY stats_table.inst_ID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

If you only need to join 2 tables and then use aggregate functions, then it's fine, as r937 said, nothing complex. Simply write

Select S.Inst_ID, count(O.Id) as CountOutput, SUM(O.Cust_Frequency) as TotalFrquency
from Stats_Table S LEFT JOIN Output_Table O
ON S.Inst_ID = O.Inst_ID
GROUP BY S.Inst_Id

When you need to join with several tables and do aggregations, then it may become tricky.

Read this blog post on the later problem

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/aggregates-with-multiple-tables

PluralSight Learning Library