CODEWITHSUNDEEP
sqlsql-serverjoin
Meir
Meir

Reputation: 12755

Adding a join to a count() query

I have the following code taken from my previous question here and changed a little.

SELECT *
FROM ES_TOOL
  INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE 
  INNER JOIN ES_PAYMENT  on ES_payment.espay_id = es_TOOL.estool_payment 

  LEFT JOIN (
    SELECT
      tchap.estch_tool, tfacet.estfa_tool,
      count(marks.esmrk_value) AmtMarks 
    FROM ES_MARK marks 
      left Join ES_TOOL_FACET tfacet ON marks.esmark_tool_facet = tfacet.estfa_id --line added
      left Join ES_TOOL_CHAPTER tchap ON marks.esmark_tool_chapter = tchap.estch_id 
    GROUP BY tchap.estch_tool 
  ) h  ON ES_TOOL.estool_id = h.estch_tool

I'm trying to add an additional join in an attempt to get a mark count from "marks" that meet either of the left join "ON" criteria. Without the extra line the query executes, but doesn't count marks that match "facet" criteria. With it I get the following error:

Msg 8120, Level 16, State 1, Line 1

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

Any help would be appreciated.

Upvotes: 0

Views: 142

Answers (4)

Andriy M
Andriy M

Reputation: 77677

My solution comes in two variants. Which one better suits you may depend on which one will yield the better execution plan when tried on your data.

Description of variant #1: (In both cases I am describing only the logic behind the main SELECT's LEFT JOIN subselect, the part that is actually becomes substituted. But the scripts come as complete queries, equivalent to yours):

  1. Pull and UNION ALL the items from both tools tables.
  2. Join the list against the marks table accordingly.
  3. Group the result set by tool items and get the counts.

The query:

SELECT *
FROM ES_TOOL
  INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE 
  INNER JOIN ES_PAYMENT  on ES_payment.espay_id = es_TOOL.estool_payment 

  LEFT JOIN (
    SELECT
      tools.tool,
      COUNT(*) AS AmtMarks
    FROM (
      SELECT 'tchap'  AS tbl, estch_id AS id, estch_tool AS tool
      FROM ES_TOOL_CHAPTER

      UNION ALL

      SELECT 'tfacet' AS tbl, estfa_id AS id, estfa_tool AS tool
      FROM ES_TOOL_FACET
    ) tools
      INNER JOIN ES_MARK marks
        ON tools.tbl = 'tchap'  AND tools.id = marks.esmark_tool_chapter
        OR tools.tbl = 'tfacet' AND tools.id = marks.esmark_tool_facet
    GROUP BY tools.tool
  ) h ON ES_TOOL.estool_id = h.tool

Variant #2:

  1. Join ES_TOOL_CHAPTER against marks and get all the estch_tool values, including duplicates.
  2. Similarly, join ES_TOOL_FACET against marks and get all the estfa_tool values, with duplicates too.
  3. UNION ALL both sets.
  4. Group the resulting set by tool items and get the counts.

And the query:

SELECT *
FROM ES_TOOL
  INNER JOIN ES_HARDWARE ON ES_HARDWARE.eshw_ID = ES_TOOL.ESTOOL_HARDWARE 
  INNER JOIN ES_PAYMENT  on ES_payment.espay_id = es_TOOL.estool_payment 

  LEFT JOIN (
    SELECT
      tools.tool,
      COUNT(*) AS AmtMarks
    FROM (
      SELECT estch_tool AS tool
      FROM ES_TOOL_CHAPTER tools
        INNER JOIN ES_MARK marks ON tools.estch_id = marks.esmark_tool_chapter

      UNION ALL

      SELECT estfa_tool AS tool
      FROM ES_TOOL_FACET tools
        INNER JOIN ES_MARK marks ON tools.estfa_id = marks.esmark_tool_facet
    ) tools
    GROUP BY tools.tool
  ) h ON ES_TOOL.estool_id = h.tool

Upvotes: 0

d-live
d-live

Reputation: 8036

There is a syntax error in this query - 

SELECT  
            tchap.estch_tool, 
            tfacet.estfa_tool,  
            count(marks.esmrk_value) AmtMarks 
        FROM ES_MARK marks   
            left Join ES_TOOL_FACET tfacet ON 
                marks.esmark_tool_facet = tfacet.estfa_id --line added  
            left Join ES_TOOL_CHAPTER tchap ON 
                marks.esmark_tool_chapter = tchap.estch_id  
        GROUP BY tchap.estch_tool

GROUP BY mandates that any column appearing in SELECT list should either be aggregated or appear in GROUP BY clause.

So put an aggregate function - MIN, MAX, SUM, AVG etc on tfacet.estfa_tool because it does not appear in group by clause or include it there.

Upvotes: 1

Stephen Chung
Stephen Chung

Reputation: 14605

This should be obvious, in your inner query:

SELECT  tchap.estch_tool, tfacet.estfa_tool,  count(marks.esmrk_value) AmtMarks 
FROM ES_MARK marks 
  left Join ES_TOOL_FACET tfacet ON marks.esmark_tool_facet = tfacet.estfa_id --line added
  left Join ES_TOOL_CHAPTER tchap ON marks.esmark_tool_chapter = tchap.estch_id 
GROUP BY tchap.estch_tool

you have three selected columns, estch_tool which is in the GROUP BY clause, esmrk_value which is in an aggregate function, and estfa_tool which is neither in the GROUP BY clause nor in an aggregate function.

Your solution should be either:

  1. GROUP BY tchap.estch_tool, tfacet.estfa_tool
  2. AVG(tfacet.estfa_tool) or any aggregate function

Upvotes: 1

Jon Egerton
Jon Egerton

Reputation: 41549

The error message means that ES_TOOL_FACET.estfa_tool needs to be included in the Group By.

When you use Group By, all non-aggregated columns must be included in the group by section.

Upvotes: 1

Related Questions