0

I wanted to be able to grab related APVM.Name data from this query however I get the error:

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

When I add APVM.Name to the GROUP BY clause, the data starts to lose its accuracy. How can I get the APVM.Name data without this error? Thank you in advance!

SELECT
    bSLHD.SL as [SL],
    APVM.Vendor as [Vendor Number],
    bSLHD.Job as [Job Number],
    JCCM.Department,
    APVM.Name
FROM
    bSLHD 
    
    INNER JOIN SLCT ON
        bSLHD.SL = SLCT.SL
        AND
        bSLHD.VendorGroup = SLCT.VendorGroup
        AND
        bSLHD.Vendor = SLCT.Vendor
        AND
        bSLHD.SLCo = SLCT.SLCo
    
    INNER JOIN APVM ON bSLHD.Vendor = APVM.Vendor
    INNER JOIN JCCM ON bSLHD.Job = JCCM.Contract AND bSLHD.JCCo = JCCM.JCCo
    
WHERE
    JCCM.Department = '10'
    
GROUP BY
    bSLHD.SL,
    APVM.Vendor,
    bSLHD.Job,
    JCCM.Department
    
HAVING
    SUM(CASE WHEN SLCT.CompCode = 'LI' THEN 1 ELSE 0 END) = 0

ORDER BY
    bSLHD.SL
Dai
  • 141,631
  • 28
  • 261
  • 374
dev_help
  • 119
  • 10
  • Check this out: https://stackoverflow.com/questions/11991079/select-a-column-in-sql-not-in-group-by – Brad Dec 09 '20 at 21:01
  • (I know others will disagree...) You should only use `GROUP BY` with _natural group-key columns_ (i.e. only columns that describe each group, and *not* with general data). To get non-key data when doing a `GROUP BY` you need to `JOIN` the results with your target data. – Dai Dec 09 '20 at 21:03
  • 2
    This sounds like a data quality issue. If you are getting more rows with `Name` in the `GROUP BY` clause, it's because there are names that have more than one vendor number, or names with more than one job. – qotsa42 Dec 09 '20 at 21:05
  • You should give a jolly good slapping to whoever is responsible for the horribly cryptic names of your tables. What on earth is `bSLHD` supposed to mean? – Dai Dec 09 '20 at 21:09
  • What are the `PRIMARY KEY` columns in each table/view/subquery? – Dai Dec 09 '20 at 21:10
  • 1
    *"When I add `APVM.Name` to the `GROUP BY` clause, the data starts to lose its accuracy."* This isn't true at all, the query is still completely as accurate; it's your understanding of how a `GROUP BY` works that is inaccurate. – Thom A Dec 09 '20 at 21:18
  • Without sample data, desired results, and a clear explanation, I don't think this question can be answered. – Gordon Linoff Dec 09 '20 at 22:18
  • @qotsa42 You were correct. There were vendors with different names associated with two different companies. One being our production company and the other being a test company. Filtering those values out resolved. Thank you. – dev_help Dec 10 '20 at 15:55

2 Answers2

0

Try replacing APVM.Name to Max(APVM.Name) or any other aggregate if u wanna see distinct names under the ongoing hierarchy. This is just a work around meaning if this output suits your requirements in terms of name getting repeats won't serve any grouping aggregations just that it would be the result of grouped entities with max names (mainly distinct)

Himanshu
  • 3,830
  • 2
  • 10
  • 29
  • This won't help, in fact it will make matters _worse_, if `APVM.Name` is not distinct in each group. Especially if there's case-sensitive collation being used. – Dai Dec 09 '20 at 21:06
  • No that won't cause any issues coz the select runs at the last in sql execution order so after all that grouping with the same logic it would just reduce the rows when it repeats – Himanshu Dec 09 '20 at 21:11
  • yes, but the OP said that when they did add `GROUP BY APVM.Name` they encountered unwanted results - which suggests they have bad data in their databas - or they don't understand how `GROUP BY` works in the first place... – Dai Dec 09 '20 at 21:16
0

There were vendors with different names associated with two different companies. One being our production company and the other being a test company. Filtering those values out resolved. Thank you.

dev_help
  • 119
  • 10