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.
我想将AccountCancellation_Process中的count(*)组合到上面的内连接语句中,这样查询就会给我Franchise表的FranchiseName,Initials,StoreNo和AccountCancellation_Process的Store_Num的结果,总记录和进度列的总记录不是空值。
how do you combine the query result with count function result?
如何将查询结果与计数函数结果相结合?
thank.
2 个解决方案
#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)
#2
Alias the count(*) then use a Sum(alias)
别名计数(*)然后使用Sum(别名)
#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)
#2
Alias the count(*) then use a Sum(alias)
别名计数(*)然后使用Sum(别名)