SQL Query: How do you combine count function result into a select query?

Question

select distinct Franchise.FranchiseName, Franchise.Initials, Franchise.StoreNo, AccountCancellation_Process.Store_Num 
FROM FranchiseData
INNER JOIN AccountCancellation_Process
on FranchiseData.StoreNo = AccountCancellation_Process.Store_Num

select count(*) from AccountCancellation_Process where Store_Num = '1234' 
select count(*) from AccountCancellation_Process where Store_Num = '1234' and Progress is not null

I want to combine the count(*) from AccountCancellation_Process into the above inner join statement so the query will give me the result of FranchiseName, Initials, StoreNo from Franchise table and Store_Num from the AccountCancellation_Process with the total records and total record with Progress column not null.

how do you combine the query result with count function result?

thank.

Answer 1

Alias the count(*) then use a Sum(alias)

Answer 2

Like this I think is what you want. I created two table value correlated subqueries to get the data based on the stored number in the inner join table. Then I join them based on the store number. That way the distinct will work. But you might also be able to do the counts in the select part with using just correlated subqueries. I was worried the distinct might not work though.

SELECT DISTINCT Franchise.FranchiseName, Franchise.Initials, Franchise.StoreNo, acp.Store_Num, total_count.Total_Count, progress_count.Progress_Count
FROM FranchiseData
     INNER JOIN AccountCancellation_Process AS acp ON (FranchiseData.StoreNo = acp.Store_Num)
     INNER JOIN (SELECT Store_Num, COUNT(*) AS Total_Count FROM AccountCancellation_Process WHERE Store_Num = acp.Store_Num) AS total_count ON (acp.Store_Num = total_count.Store_Num)
     INNER JOIN (SELECT Store_Num, COUNT(*) AS Progress_Count FROM AccountCancellation_Process WHERE Store_Num = acp.Store_Num AND Progress IS NOT NULL) AS progress_count ON (acp.Store_Num = progress_count.Store_Num)