列'columnName'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中

I have two database tables, project and story. The one project having different types of story. I wanted to display all project with each type story count.

我有两个数据库表,项目和故事。一个项目有不同类型的故事。我希望显示每个类型故事计数的所有项目。

This is my SQL:

这是我的SQL:

SELECT
     p.id,
     p.proj_id,
     p.proj_title,
     p.proj_status,
     COUNT(CASE WHEN s.story_type = 'TY_FEATURES' THEN 1 END) AS FEATURES,
     COUNT(CASE WHEN s.story_type = 'TY_ENHANCEMENTS' THEN 1 END) AS ENHANCEMENTS,
     COUNT(CASE WHEN s.story_type = 'TY_DEPENDENCIES' THEN 1 END) AS DEPENDENCIES,
     COUNT(CASE WHEN s.story_type = 'TY_ADHOC' THEN 1 END) AS ADHOC,
     COUNT(CASE WHEN s.story_type = 'TY_DEMO' THEN 1 END) AS DEMO,
     COUNT(CASE WHEN s.story_type = 'TY_IMPROVEMENTS' THEN 1 END) AS IMPROVEMENTS
FROM
     project p LEFT JOIN story s
ON
     p.proj_id = s.proj_id
GROUP BY
     p.id,
     p.proj_id,
     p.proj_status,
     p.proj_title

The SQL Query working fine, but I wanted to display all column from project table. If the column added in SELECT like p.newExistingInTable. I need to add in GROUP BY too, how can I make flexible? or any alternative optimized SQL query?

SQL Query工作正常,但我想显示项目表中的所有列。如果在SELECT中添加了列,如p.newExistingInTable。我也需要添加GROUP BY,我怎样才能灵活变通?或任何替代优化的SQL查询?

Error showing if new column added only in SELECT:

如果仅在SELECT中添加新列,则显示错误:

Column 'project.newExistingInTable' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

列'project.newExistingInTable'在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中。

2 个解决方案

#1

You can generate the story summaries inside a subquery:

您可以在子查询中生成故事摘要:

SELECT
    p.id,
    p.proj_id,
    p.proj_title,
    p.proj_status,
    FEATURES,
    ENHANCEMENTS,
    DEPENDENCIES,
    ADHOC,
    DEMO,
    IMPROVEMENTS
FROM
    project p LEFT JOIN
    (SELECT proj_id,
       COUNT(CASE WHEN story_type = 'TY_FEATURES' THEN 1 END) AS FEATURES,
       COUNT(CASE WHEN story_type = 'TY_ENHANCEMENTS' THEN 1 END) AS ENHANCEMENTS,
       COUNT(CASE WHEN story_type = 'TY_DEPENDENCIES' THEN 1 END) AS DEPENDENCIES,
       COUNT(CASE WHEN story_type = 'TY_ADHOC' THEN 1 END) AS ADHOC,
       COUNT(CASE WHEN story_type = 'TY_DEMO' THEN 1 END) AS DEMO,
       COUNT(CASE WHEN story_type = 'TY_IMPROVEMENTS' THEN 1 END) AS IMPROVEMENTS
   FROM story
   GROUP BY proj_id) s
        ON
             p.proj_id = s.proj_id

#2

-1

You could use individual subqueries for each count:

您可以为每个计数使用单个子查询:

SELECT id,proj_id, proj_title,proj_status,
   -- any other columns from project ...
   (SELECT COUNT(*) FROM story s 
    WHERE s.story_type='TY_FEATURES'  
    AND s.proj_id=p.proj_id) FEATURES,
   (SELECT ... other story counts ) colname
 FROM project p

That way no JOIN to story is necessary and you don't need group by either. But of course you need to link each subquery properly to the main record by two where conditions.

这样就没有必要加入故事,你也不需要分组。但是当然你需要将每个子查询正确地链接到两个条件的主记录。

#1