[solved] SQL - count from select just one field and not the whole record

[Nicole]

I was happy too early
This is my query, which does not work as I hoped it would do:

Code: MySQL  [Select][+][-]

1. SELECT a.FK_KONTRAKT, a.DATUM, a.C
2. FROM TBKURSZEILEN a
3. WHERE
4.     a.FK_KONTRAKT = '2369' or  a.FK_KONTRAKT = '2340'
5.     GROUP BY a.DATUM, a.FK_KONTRAKT,  a.C
6. having count(a.datum) = 1
7. order by datum

How can I make it work, that double entries in date are counted as 2 instead of as 1?
See screenshot.

In other words:
There shall be counted in Count nothing but the datum itself, not the whole record.
What I want to do: Find in my queries pairs of datum.
And skip what has not a pair of datum.

[rvk]

You can't group over a column which isn't unique.
Group only works for al same columns.
(You can do this with MySQL but not with Firebird)

For example, in your sample, what value should have been displayed for C?

You can change column C to max(C) (or sum(C) depending on what you want) and remove it from the group clause. Then it will work and return the max value for C in your result.

[Nicole]

I do not want the max values of c or so.
I want "two" records (or one with those values) which contain fk, c and HAVE THE SAME datum.

[rvk]

I do not want the max values of c or so.
I want "two" records (or one with those values) which contain fk, c and HAVE THE SAME datum.

Then you can't use having. Having only work with group by.
And in Firebird you can only group by over all Unique columns (unlike MySQL).

You could have spared us all a lot of trouble if you mentioned your expected result in your other topic.

Also... you didn't provide a dataset again.
I've created one here:
https://dbfiddle.uk/1quhN-Eb

If you want all records individual you probably need to use a SELECT as a field.
Someone can probably restructure this to a JOIN or WITH but then you see that's it easier for us if you give us a small datasample in dbfiddle.

And here we come across one of the biggest uglyness in Firebird... you can't address a field by its alias in the WHERE clause. You need to repeat the complete field (the subselect in this case) in the WHERE again to get the count(*). That's probably why this can be better written with a JOIN or WITH. But I'm not going to unless you are more clear if this is the actual required result 

Code: SQL  [Select][+][-]

1. SELECT ID_KURSZEILE, DATUM, FK_KONTRAKT, C,
2.    (
3.    SELECT COUNT(*)
4.    FROM TBKURSZEILEN B
5.    WHERE B.DATUM=A.DATUM AND (B.FK_KONTRAKT = '5' OR  B.FK_KONTRAKT = '6')
6.    ) AS X
7. FROM TBKURSZEILEN A
8. WHERE (
9.   SELECT COUNT(*)
10.   FROM TBKURSZEILEN B
11.   WHERE B.DATUM=A.DATUM AND (B.FK_KONTRAKT = '5' OR  B.FK_KONTRAKT = '6')
12.   )=2
13.   AND (A.FK_KONTRAKT = '5' OR  A.FK_KONTRAKT = '6')
14. ORDER BY DATUM, ID_KURSZEILE

[Zvoni]

https://dbfiddle.uk/6Ckth8rT

Code: SQL  [Select][+][-]

1. WITH
2.   X AS (SELECT Datum, COUNT(*) AS Cnt
3.    FROM TBKURSZEILEN B
4.    WHERE B.FK_KONTRAKT IN ('5', '6') GROUP BY Datum)
5.  
6. SELECT A.ID_KURSZEILE, A.DATUM, A.FK_KONTRAKT, A.C, X.Cnt    
7. FROM TBKURSZEILEN A
8. INNER JOIN X ON A.Datum=X.Datum AND X.Cnt=2
9. WHERE A.FK_KONTRAKT IN ('5', '6')
10. ORDER BY A.DATUM, A.ID_KURSZEILE

[Nicole]

Thank you so much rvk and zvoni.
Both versions work perfectly all right.

A lot of your explanations is new to me. Now I do not wonder any more why I ended up in so many dead-end roads with my attempts.
ALL you mentioned as "does not work in Firebird" - I tried it before.