mysql - sql多列加上每列的总和

使用MySQL，我统计了几年时间跨多个事件（字段）的发生。然后，我按年份在列中显示它。按年份分组时，我的查询效果理想。现在，我想添加一个最后的列，以显示年份的汇总。如何包含列总数查询？

Event 2008  2009  2010  2011 total
  A     0     2    0     1     3
  B     1     2    3     0     6
etc.

这是真实的查询：

select
    count(*) as total_docs,
    YEAR(field_document_date_value) as doc_year,
    field_document_facility_id_value as facility,
    IF(count(IF(field_document_type_value ='LIC809',1, NULL)) >0,count(IF(field_document_type_value ='LIC809',1, NULL)),'-') as doc_type_LIC809,
    IF(count(IF(field_document_type_value ='LIC9099',1, NULL)) >0,count(IF(field_document_type_value ='LIC9099',1, NULL)),'-') as doc_type_LIC9099,
    IF(count(field_document_f1_value) >0,count(field_document_f1_value),'-')  as substantial_compliance,
    IF(count(field_document_f2_value) >0,count(field_document_f2_value),'-') as deficiencies_sited,
    IF(count(field_document_f3_value) >0,count(field_document_f3_value),'-') as admin_outcome_809,
    IF(count(field_document_f4_value) >0,count(field_document_f4_value),'-') as unfounded,
    IF(count(field_document_f5_value) >0,count(field_document_f5_value),'-') as substantiated,
    IF(count(field_document_f6_value) >0,count(field_document_f6_value),'-') as inconclusive,
    IF(count(field_document_f7_value) >0,count(field_document_f7_value),'-') as further_investigation,
    IF(count(field_document_f8_value) >0,count(field_document_f8_value),'-') as admin_outcome_9099,
    IF(count(field_document_type_a_value) >0,count(field_document_type_a_value),'-') as penalty_type_a,
    IF(count(field_document_type_b_value) >0,count(field_document_type_b_value),'-') as penalty_type_b,
    IF(sum(field_document_civil_penalties_value) >0,CONCAT('$',sum(field_document_civil_penalties_value)),'-') as total_penalties,
    IF(count(field_document_noncompliance_value) >0,count(field_document_noncompliance_value),'-') as total_noncompliance

from rcfe_content_type_facility_document

where YEAR(field_document_date_value) BETWEEN year(NOW()) -9 AND year(NOW())
  and field_document_facility_id_value = :facility

group by doc_year

最佳答案

您不能在GROUP中两次SELECT行，因此只能计数一年或总计的行。您可以UNION两个SELECT（一个按年分组，第二个不分组-合计）以克服此限制，但是我认为最好在脚本中从年结果中计算合计。

简化示例：

SELECT by_year.amount, years.date_year FROM

-- generating years pseudo table
(
    SELECT 2008 AS date_year
    UNION ALL SELECT 2009
    UNION ALL SELECT 2010
    UNION ALL SELECT 2011
) AS years

-- joining with yearly stat data
LEFT JOIN
(
    SELECT SUM(value_field) AS amount, YEAR(date_field) AS date_year FROM data
    GROUP BY YEAR(date_field)
) AS by_year USING(date_year)

-- appending total
UNION ALL SELECT SUM(value_field) AS amount, 'total' AS date_year FROM data