sql - 汇总案例

Question

是否有可能为我需要为客户的不同标志获取计数和销售额的案例语句提供 2 个聚合函数

基本查询：

Select count(distinct invoices), sum(tot_sal_amt) from stores

试：

    Select date,case count(invoices)
when (sum(case when custom_flag='guest' then tot_sal_amt  end)then count(invoices) end 
when (sum(case when custom_flag='non-guest' then tot_sal_amt  end)then count(invoices) end 
when (sum(case when custom_flag is null then tot_sal_amt  end)then count(invoices) end
from stores

任何帮助表示赞赏。

谢谢

标签： sql

Answer 1

我很确定你想要条件聚合：

Select date, 
       count(distinct invoiceid) as num_invoices,
       count(distinct case when custom_flag = 'guest' then invoiceid end) as guest_invoices,
       count(distinct case when custom_flag = 'non-guest' then invoiceid end) as nonguest_invoices,
       count(distinct case when custom_flag is null then invoiceid end) as null_invoices,
       sum(total_sal_amt) as total_sales,
       sum(case when custom_flag = 'guest' then total_sal_amt end) as guest_total_sal_amt,
       sum(case when custom_flag = 'non-guest' then total_sal_amt end) as nonguest_total_sal_amt,
       sum(case when custom_flag is null then total_sal_amt end) as null_total_sal_amt
from stores
group by date;

或者更简单地说：

Select date, custom_flag,
       count(distinct invoiceid) as num_invoices,
       sum(total_sal_amt) as total_sales,
from stores
group by date, custom_flag;