Avatar billede Goose Juniormester
19. august 2019 - 19:32 Der er 4 kommentarer

Count baseret på datoer

Jeg sidder og bokser med noget jeg ikke kan få til at virke, og jeg kan ikke gennemskue hvorfor.

Jeg vil gerne lave en sammentælling af de records, hvor donedag1.dag1dato er forskellig fra TBL_kursus_tilmelding.kursusdag, men det funker ikke, og jeg har snart prøvet alt.
I min database er datofelterne af typen DATE og i min testdatabase ved jeg, at den burde spytte tallet 2 ud, men det skriver bare 0 :(

Det her er mit seneste forsøg:

SELECT
  TBL_kursus_tilmelding.id,
  TBL_kursus_tilmelding.kursusnr,
 
  COALESCE(donedag1, 0) AS donedag1,
  COALESCE(totaltilmeldt, 0) AS totaltilmeldt,
  COALESCE(kursusnoter, 0) AS kursusnoter

FROM TBL_kursus_tilmelding


  LEFT JOIN (SELECT
      kursusid, COUNT(*) AS totaltilmeldt
    FROM TBL_kursustilmelding_order
    WHERE slettet = 0
    GROUP BY TBL_kursustilmelding_order.kursusid) totaltilmeldt
    ON totaltilmeldt.kursusid = TBL_kursus_tilmelding.id
 
  LEFT JOIN (SELECT
    kursusid, dag1dato, COUNT(*) AS donedag1
    FROM TBL_kursustilmelding_order
    WHERE slettet = 0
    AND dag1 = 'checked')
    donedag1 ON donedag1.kursusid = TBL_kursus_tilmelding.id
    AND date_format(donedag1.dag1dato, '%Y-%m-%d') <> date_format(TBL_kursus_tilmelding.kursusdag, '%Y-%m-%d')
 
  LEFT JOIN (SELECT
      kursusid,
      COUNT(*) AS kursusnoter
    FROM TBL_kursus_noter
    WHERE slettet = 0
    AND laest = 0
    AND forfatter > 0
    GROUP BY TBL_kursus_noter.kursusid) kursusnoter
    ON kursusnoter.kursusid = TBL_kursus_tilmelding.id

WHERE TBL_kursus_tilmelding.slettet = 0
GROUP BY 1
ORDER BY TBL_kursus_tilmelding.id
Avatar billede arne_v Ekspert
19. august 2019 - 19:51 #1
Det er jo en lang SQL at gennemskue uden tabelstruktur og test data.

Nogle spredte tanker:

GROUP BY 1

virker meget forkert. Den vaelger bare en tilfaeldig raekke ud af flere raekker.

date_format(donedag1.dag1dato, '%Y-%m-%d') <> date_format(TBL_kursus_tilmelding.kursusdag, '%Y-%m-%d')

maa kunne laves som:

donedag1.dag1dato <> TBL_kursus_tilmelding.kursusdag

hvis de er DATE og ikke DATETIME.

COALESCE(donedag1, 0) AS donedag1,
  COALESCE(totaltilmeldt, 0) AS totaltilmeldt,
  COALESCE(kursusnoter, 0) AS kursusnoter

de COALESCE virker ogsaa mystiske - kan de felter vaere NULL og er 0 i givet fald en rigtigt vaerdi.
Avatar billede Goose Juniormester
19. august 2019 - 20:05 #2
Ja jeg ved det er svært, og jeg har sikkert heller ikke forklaret mig ordentligt :-/

Omkring DATE, så var det også sådan jeg havde gjort oprindeligt, men for at tjekke at fejlen ikke lå i datoerne, så valgte jeg at sikre mig samme format i begge tabeller, men det giver samme resultat.

Omkring COALESCE, så ja, antallet af de enkelte COUNT kan sagtens være 0 og da jeg gerne vil have dem med i output, fandt jeg frem til at det var løsningen på det.
Jeg har flere JOINS der anvender dette og de virker alle korrekt, det er kun når der kommer datoer i spil at de ikke fungerer.

Omkring GROUP BY, så jeg prøvet at definerer den præcist og helt udelade den og det giver samme resultat.

Som nævnt i en anden tråd hvor du kom med en løsning, så er jeg novice ud i MySQL men forsøger ihærdigt at forstå/lære det :-)
Avatar billede Jørgen Kirkegaard Professor
19. august 2019 - 21:20 #3
Prøv hver join for sig, og se, om det giver det ønskede antal rækker. Sæt dem derefter sammen, indtil du finder fejlen. Dét skulle gerne give et clue.
Avatar billede Goose Juniormester
21. august 2019 - 09:52 #4
Nu har jeg forsøgt om jeg kan finde fejlen, og jeg er nået frem til, at det måske har noget med GROUP BY at gøre, jeg kan bare ikke gennemskue hvordan jeg løser det.

Jeg skal prøve at uddybe så godt som muligt hvad det er jeg vil.

Jeg vil f.eks. gene lave en sammentælling af, hvor mange der har gennemført dag1 (donedag1) på et kursusforløb, men hvor de har gennemført det på et andet tidspunkt end den dato hvor det pågældende kursus finder sted.

Det forsøger jeg med følgende:

SELECT
  tabel1.id,
  tabel1.kursusnr,
  tabel1.dato1,
  tabel1.dato2,
  tabel1.dato3,
  COALESCE(donedag1, 0) AS donedag1,
  COALESCE(donedag2, 0) AS donedag2,
  COALESCE(donedag3, 0) AS donedag3

FROM tabel1

LEFT JOIN (SELECT
    kursusid, dag1dato, dag1, COUNT(*) AS donedag1
    FROM tabel2
    WHERE dag1='checked'
    GROUP BY dag1dato) donedag1
    ON donedag1.kursusid = tabel1.id AND donedag1.dag1dato <> tabel1.dato1

LEFT JOIN (SELECT
    kursusid, dag2dato, dag2, COUNT(*) AS donedag2
    FROM tabel2
    WHERE dag2='checked'
    GROUP BY dag2dato) donedag2
    ON donedag2.kursusid = tabel1.id AND donedag2.dag2dato <> tabel1.dato2

LEFT JOIN (SELECT
    kursusid, dag3dato, dag3, COUNT(*) AS donedag3
    FROM tabel2
    WHERE dag3='checked'
    GROUP BY dag3dato) donedag3
    ON donedag3.kursusid = tabel1.id AND donedag3.dag3dato <> tabel1.dato3

ORDER BY tabel1.kursusnr

Dette virker ikke rigtigt, da den så udskriver 2019-1 to gange med 1 i hver, hvor den jo gerne skulle lave en række med 2019-1 men med antallet 2

Laver jeg et manuelt udtræk som:
SELECT COUNT(*) FROM tabel2 where dag1='checked' and dag1dato <> '2019-08-01'
- får jeg tallet 2 ud som er korrekt.

Mine tabeller ser således ud:

CREATE TABLE IF NOT EXISTS `tabel1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kursusnr` varchar(250) NOT NULL,
  `dato1` date NOT NULL,
  `dato2` date NOT NULL,
  `dato3` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `tabel1` (`id`, `kursusnr`, `dato1`, `dato2`, `dato3`) VALUES
(1, '2019-1', '2019-08-01', '2019-08-02', '2019-08-03'),
(2, '2019-2', '2019-08-04', '2019-08-05', '2019-08-06'),
(3, '2019-3', '2019-08-07', '2019-08-08', '2019-08-09');

CREATE TABLE IF NOT EXISTS `tabel2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kursusid` int(11) NOT NULL,
  `navn` varchar(250) NOT NULL,
  `dag1` varchar(250) NOT NULL,
  `dag2` varchar(250) NOT NULL,
  `dag3` varchar(250) NOT NULL,
  `dag1dato` date NOT NULL,
  `dag2dato` date NOT NULL,
  `dag3dato` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `tabel2` (`id`, `kursusid`, `navn`, `dag1`, `dag2`, `dag3`, `dag1dato`, `dag2dato`, `dag3dato`) VALUES
(1, 1, 'Deltager 1', 'checked', 'checked', 'checked', '2019-08-01', '2019-08-02', '2019-08-03'),
(2, 1, 'Deltager 2', 'checked', 'checked', 'checked', '2019-08-19', '2019-08-02', '2019-08-03'),
(3, 1, 'Deltager 3', 'checked', 'checked', '', '2019-08-18', '2019-08-02', '2019-08-03');
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester