CODEWITHSUNDEEP
sqlsql-serverjoingroup-by
Patrick Rennings
Patrick Rennings

Reputation: 337

SQL sub query with group by

I have the following SQL query:

SELECT 
kvknum.cd_hfdrelnr, 
kvknum.cd_kvkanum,
    relName.cd_hfdrelnr
FROM
(
    SELECT 
        cd_hfdrelnr, 
        cd_kvkanum

FROM er_105
WHERE cd_kvkanum IN
(
    SELECT cd_kvkanum
    FROM er_105
    GROUP BY cd_kvkanum
    HAVING COUNT(*) > 1 
)
AND cd_kvkanum != ''
ORDER BY cd_kvkanum
) AS kvknum
LEFT OUTER JOIN
(
    SELECT 
        cd_hfdrelnr,
        cd_relnaam
    FROM er_101
) AS relName
ON kvknum.cd_hfdrelnr = relName.cd_hfdrelnr

The GROUP BY function is not allowed and it is needed so the same cd_kvkanum values are shown together under each other, is there a work around for this or how is this possible to achieve?

Following error comes with it:

"Msg 1033, Level 15, State 1, Line 21 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'AS'."

when I run the following query:

SELECT 
    cd_hfdrelnr, 
    cd_kvkanum

FROM er_105
WHERE cd_kvkanum IN
(
    SELECT cd_kvkanum
    FROM er_105
    GROUP BY cd_kvkanum
    HAVING COUNT(*) > 1 
)
AND cd_kvkanum != ''
ORDER BY cd_kvkanum

(First subquery of the join) the results look like:
1235 - 123
4652 - 123
8569 - 1234
4985 - 1234

Though I want to add cd_relnaam to the result list, when Just use an JOIN on the query my results are blank...

anybody knows what I do wrong?

Upvotes: 0

Views: 5842

Answers (2)

Oxydel
Oxydel

Reputation: 176

The problem here is not so much with the GROUP BY, it's actually with the ORDER BY, you can't put it in a subquery, it has to be on your parent most query. Also my understanding is that you want the records from er_105 where cd_kvkanum occurs more than once and is not blank, but you don't want to aggregate.

SELECT er_105.cd_hfdrelnr, 
       er_105.cd_kvkanum,
       er_101.cd_relnaam
  FROM er_105
  JOIN (SELECT cd_kvkanum,COUNT(cd_kvkanum)
          FROM er_105
         WHERE cd_kvkanum != ''
      GROUP BY cd_kvkanum
        HAVING COUNT(cd_kvkanum) > 1
       ) kvknum ON er_105.cd_kvkanum = kvknum.cd_kvkanum
LEFT JOIN er_101 ON er_105.cd_hfdrelnr = er_101.cd_hfdrelnr
 ORDER BY er_105.cd_kvkanum

Usually it's better when you want to count the occurences of something, to do SELECT something,COUNT(PrimaryKey) rather than COUNT(*). The * Makes SQL do the extra step of figuring out what * stands for. And of Course, like with many queries on stackoverflow, There's always a simpler way to write it ;)

Upvotes: 1

roman
roman

Reputation: 117380

It's not a complete answer, but, if I'm not mistaken, your query can be written much simpler. 90% of mistakes arise when people write very complex queries when they can write simple ones. Simplicity is the key to success :) I'll edit the answer when you gave more exact question

SELECT 
    kvknum.cd_hfdrelnr, 
    kvknum.cd_kvkanum,
    relName.cd_hfdrelnr
FROM er_105 as kvknum
    LEFT OUTER JOIN er_101 as relName on relName.cd_hfdrelnr = kvknum.cd_hfdrelnr
where
    kvknum.cd_kvkanum IN
    (
        SELECT cd_kvkanum
        FROM er_105
        GROUP BY cd_kvkanum
        HAVING COUNT(*) > 1 
    ) AND
    cd_kvkanum != ''

Upvotes: 1

Related Questions