英文:
Trying to count rows per unique ID from JOINED table - MySQL
问题
我已经将几个表格使用 JOIN 进行了合并（全部使用 LEFT JOIN 更具体）。
现在我正在尝试计算其中一个表格（表 - measures）中每个唯一参考号（主表 - referral 上的）的记录数量的条件。
一个推荐可以有多个度量，并且每个度量可以具有不同的状态（来自度量表的状态计算列）。
我正在尝试创建一个列（Test），它将计算每个推荐的所有未取消度量。
使用我目前的代码，我得到了不带有“取消”状态的度量的记录的总数，但我想按推荐拆分这个数量。
谢谢，Maria
请参阅我到目前为止的代码：
SELECT r.reference, r.status, pcla.la_name, r.project_reference,
(SELECT COUNT(DISTINCT(id))
FROM measure
WHERE status_calculation <> "Cancelled") AS "Test",
p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN measure m ON r.reference = m.reference
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
AND DATE(r.created) > "2022-03-31"
AND m.completed IS NOT NULL
AND pcla.la_name = "Ealing"
GROUP BY r.reference
ORDER BY r.reference
LIMIT 20;
这是我得到的结果：
希望有人能指导我缺少了什么。
英文:
I have combined few tables with JOIN (all LEFT JOIN to be more specific).
Now I am trying to count the number of records from one of the tables (table - measures) per
unique Reference number from main table - referral on a condition.
One referral can have more than one measure, and each measure can have different Status (col status_calculation from measure table).
I am trying to create a column (Test) which will count all non-cancelled measures per referral.
With the code I currently have I get the total number of records where a measure is not with status "Cancelled", but I want to split this per referral.
Thanks,
Maria
Please see below the code I have so far:
SELECT r.reference, r.status, pcla.la_name, r.project_reference,
(SELECT COUNT(DISTINCT(id))
FROM measure
WHERE status_calculation <> "Cancelled") AS "Test",
p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN measure m ON r.reference = m.reference
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
AND DATE(r.created) > "2022-03-31"
AND m.completed IS NOT NULL
AND pcla.la_name = "Ealing"
GROUP BY r.reference
ORDER BY r.reference
LIMIT 20;
this is the result I am getting:
Hopefully someone can advise what is it that I am missing..
答案1
得分: 0
你需要包括所有相关的引用，如下所示：
SELECT r.reference, r.status, pcla.la_name, r.project_reference,
(SELECT COUNT(DISTINCT(m1.id))
FROM measure m1
WHERE m1.status_calculation <> "Cancelled"
and m1.reference = r.reference) AS "Test",
p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN measure m ON r.reference = m.reference
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
AND DATE(r.created) > "2022-03-31"
AND m.completed IS NOT NULL
AND pcla.la_name = "Ealing"
GROUP BY r.reference
ORDER BY r.reference
LIMIT 20;
如果您需要进一步的帮助，请随时告诉我。
英文:
You need to include only all references that belong
like
SELECT r.reference, r.status, pcla.la_name, r.project_reference,
(SELECT COUNT(DISTINCT(m1.id))
FROM measure m1
WHERE m1.status_calculation <> "Cancelled"
and m1.reference = r.reference) AS "Test",
p.EPCR_GasProperty, DATE(r.created)
FROM referral r
LEFT JOIN postcode_la pcla ON r.postcode = pcla.postcode
LEFT JOIN measure m ON r.reference = m.reference
LEFT JOIN customer c ON r.reference = c.reference
LEFT JOIN survey s ON s.customer_id = c.id
LEFT JOIN property p ON p.id = s.property_id
AND DATE(r.created) > "2022-03-31"
AND m.completed IS NOT NULL
AND pcla.la_name = "Ealing"
GROUP BY r.reference
ORDER BY r.reference
LIMIT 20;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论