Stack Exchange
Having without group by, with subquery
I'm looking for the the rows that have namecounter = 3.
I tried using a where and having but both failed me, or am i doing something wrong?
This is my current SQL code:
SELECT refnam, tstring,(SELECT COUNT('refnam') FROM refdev rc
                        WHERE rc.refnam = r.refnam
                        ) as namecounter
FROM refdev r, OUTER texte t
WHERE r.sigtnr = t.textnr
ORDER BY refnam

Here's an example of my output at the moment:
AAU01   AANGEVRAAGD                                                                                             4
AAU01   ACTIEF                                                                                                  4
AAU01   UIT                                                                                                     4
AAU01   AANGEVRAAGD EN ACTIEF                                                                                   4
AB001   UIT                                                                                                     2


What is the OUTER supposed to do there? It doesn't seem valid SQL query.

Please post your schema so we don't have to guess your table structure.

The difference is 2292 vs 2268 rows. I'm sorry but i have no schema available :(

SELECT r.refnam, 
       t.tstring,
       rc.cnt namecounter,
       d.alarmzu
FROM refdev r
INNER JOIN (SELECT refnam, 
                   COUNT(*) cnt
              FROM refdev rc
          GROUP BY refnam
           ) rc 
        ON rc.refnam = r.refnam
LEFT OUTER JOIN texte t
             ON r.sigtnr = t.textnr
JOIN devtzu d ON d.zustnr = r.zustnr
LEFT OUTER JOIN refdev_DEF RD ON d.dvtypnr = RD.dvtypnr
WHERE r.refnam = RD.refnam
AND rc.cnt = 3
ORDER BY r.refnam


SELECT r.refnam, 
       t.tstring,
       rc.cnt namecounter
FROM refdev r
INNER JOIN (SELECT refnam, 
                   COUNT(*) cnt
              FROM refdev rc
          GROUP BY refnam
           ) rc 
        ON rc.refnam = r.refnam
LEFT OUTER JOIN texte t
             ON r.sigtnr = t.textnr
WHERE rc.cnt = 3
ORDER BY r.refnam


ERROR: Ambiguous column (refnam).
Error Code: -324

Then add aliases. You should have in the original query, too!

I dont understand what you mean ? the aliases here are R and RC

Sorry, I meant prefix columns with table aliases: SELECT r.refnam, t.tstring

Nice. And there is no need for COUNT('refnam'). COUNT(*) should work.

fixed the query, thanks @ypercube for the support :)

Hi guys, i'm looking for a way to add this to the code:
SELECT d.alarmzu
FROM devtzu d, OUTER refdev_DEF RD
WHERE D.zustnr = R.zustnr
AND D.dvtypnr = RD.dvtypnr AND R.refnam = RD.refnam

what are you trying to achieve? you want to get only the refnam returned from the first query?

@mucio How do you mean ? i want the first query (the one above), but this time also displaying alarmzu, and, the necessary join, + a join with refdev_DEF.

As the subquery returns a column of your query, WHERE is right for this. I don't understand the "OUTER" thing as ypercube commented.
This should function:
SELECT refnam, tstring, COUNT(*) as namecounter
FROM refdev r, texte t
WHERE r.sigtnr = t.textnr
GROUP BY refnam, tstring
HAVING COUNT(*)=3
ORDER BY refnam


ERROR: Column (namecounter) not found in any table in the query
(or SLV is undefined).
Error Code: -217

I edited the post - in my opinion there is no need for a subquery. This would function on MS SQL Server for sure, but of course I can't guarantee this for a 10yo informix database...

This would not run in SQL-Server (you use tstring in the Select list while it's not in the GROUP BY list.) And the GROUP BY might result in different number of rows than the OP's query.

@ypercube I obviously forgot tstring in the group by list, yes, and maybe the count should be changed to count(*), but then it would work out exactly like the OP wanted, and it's much more clear than the accepted answer.

I don't think so. A group by usually results in less rows (unless you group by a primary/unique key.)

Yes, a group by has the purpose of grouping things :-) As far as I understood, the OP wanted a list of refnam & tstring-combinations with the number of occurrences. Thats exactly what he gets.

Using this query i get only 4 rows as a result oO

Well, this would mean that you only have 4 different combinations of refnam and tstring that occur 3 times in your dataset. If you omit the HAVING part, you would see all the different combinations and how often they occur in your dataset.
