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.

Will Rickards · Accepted Answer · 2009-04-09 21:09:43Z

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)

Shea · Accepted Answer · 2009-04-09 21:03:07Z

1

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

answered Apr 9, 2009 at 21:03

Shea

11.3k2 gold badges21 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related