SQL sub query with group by

Question

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?

Sample data? Expected result? What you mean by "though the group by function is not allowd". Please try to add some context to the question. We're not mind readers. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 19, 2012 at 6:53

roman · Accepted Answer · 2012-10-19 06:55:29Z

1

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 != ''

answered Oct 19, 2012 at 6:55

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick Rennings Over a year ago

With the query you created the problem is the results are not "grouped by" the matching results eg you got: id - anumer

roman Over a year ago

could you please add sample data (2-3 records of er_105, er_101 table) and desired result?

Oxydel · Accepted Answer · 2012-10-20 03:43:29Z

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

Collectives™ on Stack Overflow

SQL sub query with group by

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related