不能按共享字段分组- Oracle SQL

Question

我有一项声明：

SELECT adherence_date,
  CASE scheduled_time
    WHEN 'morning' THEN CASE acknowledged_at WHEN null THEN 0 ELSE 1 END
    ELSE null END AS morning,
  CASE scheduled_time
    WHEN 'afternoon' THEN CASE acknowledged_at WHEN null THEN 0 ELSE 1 END
    ELSE null END AS afternoon,
  CASE scheduled_time
    WHEN 'evening' THEN CASE acknowledged_at WHEN null THEN 0 ELSE 1 END
    ELSE null END AS evening,
  CASE scheduled_time
    WHEN 'night' THEN CASE acknowledged_at WHEN null THEN 0 ELSE 1 END
    ELSE null END AS night
FROM MEDICATION_ADHERENCES
WHERE PATIENT_ID = 10049;

这就是输出：

[
  #<MedicationAdherence:0x007f8425947860> {
                  "id" => nil,
      "adherence_date" => Thu, 27 Oct 2016,
             "morning" => 1,
           "afternoon" => nil,
             "evening" => nil,
               "night" => nil
  },
   #<MedicationAdherence:0x007f8425947360> {
                  "id" => nil,
      "adherence_date" => Thu, 27 Oct 2016,
             "morning" => nil,
           "afternoon" => 1,
             "evening" => nil,
               "night" => nil
  },
   #<MedicationAdherence:0x007f8425946d98> {
                  "id" => nil,
      "adherence_date" => Thu, 27 Oct 2016,
             "morning" => nil,
           "afternoon" => nil,
             "evening" => 1,
               "night" => nil
  },
   #<MedicationAdherence:0x007f8425946aa0> {
                  "id" => nil,
      "adherence_date" => Thu, 27 Oct 2016,
             "morning" => nil,
           "afternoon" => nil,
             "evening" => nil,
               "night" => 1
  }
]

我希望它按adherence_date将结果分组，这样它就会返回

#<MedicationAdherence:0x007f8425946aa0> {
                "id" => nil,
    "adherence_date" => Thu, 27 Oct 2016,
           "morning" => 1,
         "afternoon" => 1,
           "evening" => 1,
             "night" => 1
}

但是当我添加GROUP BY ADHERENCE_DATE时，它返回错误OCIError: ORA-00979: not a GROUP BY expression

我在这里有什么不懂的？我在运行甲骨文-12c

编辑

MedicationAdherence记录看起来如下：

MedicationAdherence {
                 :id => :integer,
     :adherence_date => :date,
     :scheduled_time => :string,
    :acknowledged_at => :datetime,
         :patient_id => :integer,
         :created_at => :datetime,
         :updated_at => :datetime
}

Answer 1

我能解决这个问题

SELECT
  adherence_date,
  patient_id,
  sum(case when scheduled_time = 'morning'   then nvl2(acknowledged_at,1,0) end) as morning,
  sum(case when scheduled_time = 'afternoon' then nvl2(acknowledged_at,1,0) end) as afternoon,
  sum(case when scheduled_time = 'evening'   then nvl2(acknowledged_at,1,0) end) as evening,
  sum(case when scheduled_time = 'night'     then nvl2(acknowledged_at,1,0) end) as night
FROM medication_adherences
GROUP BY adherence_date, patient_id

在这个问题中回答：Aggregating multiple records by date

Answer 2

您需要对其余的列使用聚合函数。我猜你想：

SELECT adherence_date,
       SUM(CASE WHEN scheduled_time = 'morning' AND acknowledged_at is not null THEN 1 ELSE 0
           END) AS morning,
       SUM(CASE WHEN scheduled_time = 'afternoon' AND acknowledged_at is not null THEN 1 ELSE 0
           END) AS afternoon,
       SUM(CASE WHEN scheduled_time = 'evening' AND acknowledged_at is not null THEN 1 ELSE 0
           END) AS evening,
       SUM(CASE WHEN scheduled_time = 'night' AND acknowledged_at is not null THEN 1 ELSE 0
           END) AS night
FROM MEDICATION_ADHERENCES
WHERE PATIENT_ID = 10049
GROUP BY adherence_date;

嵌套的case不仅更加复杂，而且没有做您想做的事情。when null从不匹配，因为null不等于null。