每个存储区最常用的投标类型

Question

我需要一些帮助来构建这个MSSQL查询。我有一张有付款信息的桌子。表中的"payment_entry“是Storenumber和Tendertype的字段。每一次新购买都会在表"payment_entry“中创建”商店“和”招标类型“的新行。有两种投标类型"1“或"3”。1表示信用卡/借记卡，3代表现金。

我想要一个按商店分组的查询，每家商店有多少顾客付现金，有多少人用卡付款。

提前谢谢！！编辑:嗯，我不擅长SQL，但这给了我每个商店的投标类型1的计数。现在我卡住了..。

Select [company$Trans_ Payment Entry].[Store No_], 
Count([company$Trans_ Payment Entry].[Tender Type]) 
From [company$Trans_ Payment Entry] [company$Trans_ Payment Entry] 
Where [company$Trans_ Payment Entry].[Tender Type] = '1' 
Group By [company$Trans_ Payment Entry].[Store No_] 
Order By [company$Trans_ Payment Entry].[Store No_] 

Answer 1

;WITH Store_Totals AS
 (
  SELECT [Store No_] AS StoreNum
        ,SUM(CASE WHEN tendertypes = 1 THEN 1 ELSE 0 END) AS Credit_Trans
        ,SUM(CASE WHEN tendertypes = 3 THEN 1 ELSE 0 END) AS Cash_Trans
        ,COUNT(tendertypes)  AS Total_Trans
  FROM [company$Trans_ Payment Entry]
  GROUP BY [Store No_]
 )
SELECT  StoreNum
       ,Credit_Trans / Total_Trans AS Average_Credit
       ,Cash_Trans   / Total_Trans AS Average_Cash
FROM Store_Totals 

Answer 2

您可以通过基于案例的聚合来完成这一任务。

SELECT store, 
       (SUM(CASE WHEN tendertype =1 THEN 1 ELSE 0 END)*100.0/COUNT(tenderType)) AS CreditPerc,
       SUM(CASE WHEN tendertype =3 THEN 1 ELSE 0 END)*100.0/COUNT(tenderType) as CashPerc
FROM payment_entry
GROUP BY store