Tutorial :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.


Solution:1

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)  


Solution:2

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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »