使用一个查询进行多次计数，同时还考虑“计数不同”

Hey I don't know if this is even possible, but I'm trying to get a list of shops with a count of distinct customers, and then trying to calculate two further columns per shop for whether this appointment was an initial or a subsequent appointment... this is as far as I have got.

嘿，我不知道这是否可能，但我正在尝试获得一系列具有不同客户数量的商店，然后尝试计算每个商店的另外两个列，以确定此约会是初始还是后续预约......这是我所拥有的。

SELECT year,
shopname,
COUNT (DISTINCT appointmentkey) custappoint,
SUM (CASE WHEN attendance = 'initial' THEN 1 ELSE 0 END) initialapp,
SUM (CASE WHEN attendance = 'subsequent' THEN 1 ELSE 0 END) subapp
   FROM shoptable
     WHERE shopType in ('local')
     GROUP BY year, shopname
     ORDER BY year, shopname

During an appointment various things can happen, in our database this is coded for on various rows with one appointment key. Meaning that the appointmentkey turns up multiple times for one appointment, hence the code above counts the number of rows that fulfill that criteria and gives a table like;

在约会期间，可能会发生各种各样的事情，在我们的数据库中，这是使用一个约会密钥在各行上编码的。这意味着，对于一次约会，约会键会多次出现，因此上面的代码会计算满足该条件的行数，并给出一个像这样的表格;

shopname | custappoint | initialapp | subapp

-------- | ----------- | ---------- | ------

 local1  |    44       |   48       | 89

The desired result is that sum of the initial and subsequent appointments should but equal to the number of total appointments. I was trying to add in 'distinct' to the case when counts, but don't think this it the correct route. Although I'm sure someone must have had a similar instance I can't find anything similar across the web so turning to you guys!

期望的结果是初始和后续约会的总和应该等于总约会的数量。我试图在计数时添加“不同”的情况，但不要认为这是正确的路线。虽然我确定某人必须有类似的实例，但我在网上找不到类似的东西，所以转向你们！

Thanks

谢谢

1 个解决方案

#1

If each appointmentkey can only have one possible attendance value, then you can simply select from a derived table of distinct rows:

如果每个约会键只能有一个可能的出勤值，那么您只需从不同行的派生表中进行选择：

SELECT year,
shopname,
COUNT (appointmentkey) custappoint,
SUM (CASE WHEN attendance = 'initial' THEN 1 ELSE 0 END) initialapp,
SUM (CASE WHEN attendance = 'subsequent' THEN 1 ELSE 0 END) subapp
FROM (
   SELECT DISTINCT year, shopname, appointmentkey, attendance
   FROM shoptable
     WHERE shopType in ('local')
     GROUP BY year, shopname
     ORDER BY year, shopname
) t