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

----------------------------------------------------------------------
Nicole · 2024-03-07 15:01

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

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
a.FK_KONTRAKT, a.DATUM, a.CFROM TBKURSZEILEN a WHERE    a.FK_KONTRAKT
= '2369' or  a.FK_KONTRAKT = '2340'     GROUP BY a.DATUM,
a.FK_KONTRAKT,  a.C having count(a.datum) = 1order 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 · 2024-03-07 15:19

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 · 2024-03-07 15:41

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 · 2024-03-07 16:08

Quote from: Nicole on March 07, 2024, 04:41:33 pmI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
ID_KURSZEILE, DATUM, FK_KONTRAKT, C,   (   SELECT COUNT(*)   FROM
TBKURSZEILEN B   WHERE B.DATUM=A.DATUM AND (B.FK_KONTRAKT = '5' OR
 B.FK_KONTRAKT = '6')   ) AS XFROM TBKURSZEILEN AWHERE (  SELECT
COUNT(*)  FROM TBKURSZEILEN B  WHERE B.DATUM=A.DATUM AND
(B.FK_KONTRAKT = '5' OR  B.FK_KONTRAKT = '6')  )=2   AND
(A.FK_KONTRAKT = '5' OR  A.FK_KONTRAKT = '6')ORDER BY DATUM,
ID_KURSZEILE

----------------------------------------------------------------------
Zvoni · 2024-03-07 16:24

https://dbfiddle.uk/6Ckth8rT

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i WITH  X AS
(SELECT Datum, COUNT(*) AS Cnt    FROM TBKURSZEILEN B   WHERE
B.FK_KONTRAKT IN ('5', '6') GROUP BY Datum) SELECT A.ID_KURSZEILE,
A.DATUM, A.FK_KONTRAKT, A.C, X.Cnt    FROM TBKURSZEILEN A INNER JOIN X
ON A.Datum=X.Datum AND X.Cnt=2WHERE A.FK_KONTRAKT IN ('5', '6')ORDER
BY A.DATUM, A.ID_KURSZEILE

----------------------------------------------------------------------
Nicole · 2024-03-07 17:09

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.