Statistik Abfrage

[Simuletsplay]

Hallo zusammen,

ich habe ein Problem mit der Abfrage in meiner SQL-DB.

Ich habe eine Tabelle mit Mitgliedern, bei denen ich jeweils das Eintrittsdatum im Format Y-m-d habe.
Zur Auswertung möchte ich jetzt gerne wissen, in welchem Monat wie viele Mitglieder dazugekommen sind.

Also
Monat Anzahl
2017/1 2
2017/2 0
2017/3 5
...
2022/1 4
...

Ich habe folgende Abfrage probiert

SELECT COUNT(id) AS counter, MONTH(mitglied_seit) AS monat, YEAR(mitglied_seit) AS year FROM users GROUP BY MONTH(mitglied_seit)

Jedoch gibt er mir JEDE Jahreszahl (einmalig) wieder und dazu einen Monat und eine Anzahl:




Wo habe ich einen Fehler gemacht?

Edit: Die Abfrage wurde nochmal überarbeitet. Ich hatte aus Versehen das Falsche gepostet.

 

[akretschmer]

Wo habe ich einen Fehler gemacht?

MySQL erkennt den logischen Fehler in Deiner Abfrage nicht: bei einer Aggregation, hier count(), müssen alle Spalten des Resultsets entweder aggregiert oder gruppiert werden.

 

[Simuletsplay]

OK Danke dir.
Mit

SELECT COUNT(id) AS counter, MONTH(mitglied_seit) AS monat, YEAR(mitglied_seit) AS year FROM users GROUP BY YEAR(mitglied_seit), MONTH(mitglied_seit)

hat es geklappt!

 

[akretschmer]

OK Danke dir.
Mit

SELECT COUNT(id) AS counter, MONTH(mitglied_seit) AS monat, YEAR(mitglied_seit) AS year FROM users GROUP BY YEAR(mitglied_seit), MONTH(mitglied_seit)

hat es geklappt!

Na schau an!

Das tolle an diesem Bug in MySQL ist, daß es ein Alleinstellungsmerkmal ist. Keine andere Datenbank liefert statt einer Fehlermeldung ein Zufallsergebniss. Ideal für Statistiken, gell?

 

[Kampfgummibaerlie]

erst einmal eine Tabelle für Mitglieder erstellen:

test=# create table mitglieder (id serial primary key, name text not null, eintragsdatum date default now()::date);
CREATE TABLE

Mitglieder einfügen:

test=# insert into mitglieder(name, eintragsdatum) values ('1', '2022-01-01');
INSERT 0 1

test=# insert into mitglieder(name, eintragsdatum) values ('1', '2022-02-01');
INSERT 0 1

test=# insert into mitglieder(name, eintragsdatum) values ('1', '2022-02-01');
INSERT 0 1

test=# insert into mitglieder(name, eintragsdatum) values ('1', '2022-03-01');
INSERT 0 1

Dann eine Abfrage, wo sich das selbe Monat über mehrere Jahre summiert.

test=# insert into mitglieder(name, eintragsdatum) values ('1', '2021-03-02');
INSERT 0 1
test=# select count(name) from mitglieder group by (select extract(month from(eintragsdatum)));
 count
-------
     1
     2
     2
(3 Zeilen)

Und eine passende Abfrage, die nach beiden Kriterien sortiert (Monat und Jahr):

test=# select count(name) from mitglieder group by (select extract(month from(eintragsdatum))), (select extract(year from(eintragsdatum)));
 count
-------
     2
     1
     1
     1
(4 Zeilen)

Ich hoffe, ich konnte helfen

 

[akretschmer]

Um das Beispiel von @Kampfgummibaerlie etwas aufzupeppen:

test=# create table simuletsplay(datum date);
CREATE TABLE
test=# insert into simuletsplay select '2021-01-01'::date + random() * 200 * '1day'::interval from generate_series(1,100) s;
INSERT 0 100
test=# select date_trunc('month', datum), count(*) from simuletsplay group by 1 order by 1;
       date_trunc       | count 
------------------------+-------
 2021-01-01 00:00:00+00 |    14
 2021-02-01 00:00:00+00 |    15
 2021-03-01 00:00:00+00 |    18
 2021-04-01 00:00:00+00 |    11
 2021-05-01 00:00:00+00 |    12
 2021-06-01 00:00:00+00 |    22
 2021-07-01 00:00:00+00 |     8
(7 rows)

Ja, das ist jetzt NICHT mit MySQL gemacht.

Lasse ich das GROUP BY weg, passiert dafür dies:

test=# select date_trunc('month', datum), count(*) from simuletsplay order by 1;
ERROR:  column "simuletsplay.datum" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select date_trunc('month', datum), count(*) from simuletspla...
                                   ^
test=#

bzw.:

test=# select extract(year from datum), extract(month from datum), count(*) from simuletsplay group by 1,2 order by 1,2;
 extract | extract | count 
---------+---------+-------
    2021 |       1 |    14
    2021 |       2 |    15
    2021 |       3 |    18
    2021 |       4 |    11
    2021 |       5 |    12
    2021 |       6 |    22
    2021 |       7 |     8
(7 rows)

test=# select extract(year from datum), extract(month from datum), count(*) from simuletsplay group by 1 order by 1,2;
ERROR:  column "simuletsplay.datum" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...lect extract(year from datum), extract(month from datum), co...
                                                             ^
test=#

 

[BerndB]

Du kannst auch DATE_FORMAT() für dein GROUP BY nehmen

SELECT  COUNT(id) AS counter
              , MONTH(mitglied_seit) AS monat
              , YEAR(mitglied_seit) AS year
FROM users
GROUP BY DATE_FORMAT(mitglied_seit, '%Y%m');

 

[Kampfgummibaerlie]

count id geht nicht...
weil diese der Primary Key sein sollte, und sich dem entsprechend nicht wiederholt....

(ich glaube, mich an sowas zu erinnern ^^)

EDIT: mein fehler, ich dachte verkehrt... ja könnte gehen