Adding a join to a count() query

Question

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.

If you can include some example input and output data in your question it'll be easier to be specific about the answer. — Jon Egerton, Mar 14 '11 at 08:55
Can `tchap.estch_tool` and `tfacet.estfa_tool` hold same values? If they can, should the same values' matches be counted together? — Andriy M, Mar 14 '11 at 09:45

score 1 · Answer 1 · answered Mar 14 '11 at 08:55

1

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.

answered Mar 14 '11 at 08:55

Jon Egerton

40,401
11
97
129

score 1 · Answer 2 · answered Mar 14 '11 at 08:56

1

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:

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

answered Mar 14 '11 at 08:56

Stephen Chung

14,497
1
35
48

I tried: GROUP BY tchap.estch_tool, tfacet.estfa_tool , but then the query returns 2 lines for each tool with chapter and facet matches, whereas I need one line for each tool with a total count for both chapter and facet matches. Ideas? – Meir Mar 14 '11 at 09:08
Well, you'll have to debug your query then. Perhaps somewhere you have joined in a table which expands the result set by 2 (in that case you should be missing a WHERE clause)? Ah, the fun of debugging and tuning SQL... – Stephen Chung Mar 14 '11 at 09:16

d-live · Answer 3 · 2011-03-14T09:07:31.343

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.

oh my.. i am so slow at typing... there two answers already!! — d-live, Mar 14 '11 at 09:02

score 0 · Accepted Answer · answered Mar 14 '11 at 12:50

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):

Pull and UNION ALL the items from both tools tables.
Join the list against the marks table accordingly.
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:

Join ES_TOOL_CHAPTER against marks and get all the estch_tool values, including duplicates.
Similarly, join ES_TOOL_FACET against marks and get all the estfa_tool values, with duplicates too.
UNION ALL both sets.
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

Adding a join to a count() query

4 Answers4