Probleem met query opbouw
Code (php)
1
2
3
4
2
3
4
ID Betaald Betaald_op betaald_in_maand Bedrijfsnaam Bedrag Recurrent Categorie
1 ja 2015-11-10 november Bedrijf1 100 nee IT materiaal
2 nee Bedrijf2 50 nee Software
3 ja 2014-12-20 december Bedrijf3 250 ja Mobiele kosten
1 ja 2015-11-10 november Bedrijf1 100 nee IT materiaal
2 nee Bedrijf2 50 nee Software
3 ja 2014-12-20 december Bedrijf3 250 ja Mobiele kosten
En een tabel genaamd lijst_accountics_categorie:
Code (php)
1
2
3
4
5
2
3
4
5
ID naam
1 Restaurant
2 Mobiele kosten
3 Software
4 IT materiaal
1 Restaurant
2 Mobiele kosten
3 Software
4 IT materiaal
De relatie tussen beide tabellen zit in de categorie (tabel accountics) en de naam (tabel lijst_accountics_categorie).
Wat ik nu wil is een overzichtlijst zoals hieronder:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
Code jan feb mrt apr mei jun jul aug sep okt nov dec
Recurrent gedeelte
Mobiele kosten 250
Eenmalig gedeelte
IT-materiaal 100
Recurrent gedeelte
Mobiele kosten 250
Eenmalig gedeelte
IT-materiaal 100
De namen 'recurrent gedeelte' en 'eenmalig gedeelte' komt uit de selectie in de accountics tabel of het recurrent is of niet. Bvb: recurrent=ja => moet bij recurrent gedeelte gevoegd worden, recurrent=nee => moet bij eenmalig gedeelte gevoegd worden.
Kosten die nog niet betaald zijn (betaald = nee) moeten niet in de lijsten verschijnen.
Zijn er meerdere kosten in dezelfde maand onder dezelfde categorie worden die gewoon samengeteld voor die maand.
Momenteel heb ik al het volgende gedaan:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT
SUM(totaal_incl) as tot_be_jan
FROM
accountics a
JOIN
lijst_accountics_categorie lc
ON
a.categorie = lc.naam
WHERE a.betaald_in_maand = 'januari'
SUM(totaal_incl) as tot_be_jan
FROM
accountics a
JOIN
lijst_accountics_categorie lc
ON
a.categorie = lc.naam
WHERE a.betaald_in_maand = 'januari'
En dit doe ik per maand
Daaronder doe ik dan de volgende query:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT
a.categorie
FROM
accountics a
JOIN
lijst_accountics_categorie lc
ON
a.categorie = lc.naam
WHERE a.recurrent = 'nee'
a.categorie
FROM
accountics a
JOIN
lijst_accountics_categorie lc
ON
a.categorie = lc.naam
WHERE a.recurrent = 'nee'
Hieronder komt dan de html tabel te staan. Waarschijnlijk kunnen deze beiden in 1 query uitgevoerd worden. Dan moet er nog een opsplitsing gebeuren van dat recurrent en eenmalig gedeelte ook. Ook de totalen zijn niet juist opgeteld hiermee. Alles wat in de maand januari staat bvb is samen opgeslagen maar niet per categorie wat dus ook niet juist is.
Mededeling:
Dit is ook gepost op: http://www.pfz.nl/forum/topic/11087-probleem-met-query-opbouw/#entry75237
Gewijzigd op 21/01/2015 16:58:29 door Brecht S
Maar toch een paar opmerkingen/tips.
Op de eerste plaats begrijp ik niet waarom je de tabel categorie hebt. Het zou logischer zijn geweest om in accountics ipv de categorie naam de categorie id te hebben opgeslagen (bijv 1 ipv Restaurant).
Bedenk maar eens wat je nu moet doen als je Restaurant wilt wijzigen Cafetaria.
Volgens mij hoef je ook geen aparte query per maand te doen, je kunt dat ook in 1 query doen die 12 maandkolommen teruggeeft.
Bijv. iets als:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
Recurrent,
Categorie,
SUM(CASE WHEN betaald_in_maand = 'januari' THEN totaal_inc ELSE 0) as januari,
SUM(CASE WHEN betaald_in_maand = 'februari' THEN totaal_inc ELSE 0) as februari,
... de andere maanden ...
SUM(CASE WHEN betaald_in_maand = 'december' THEN totaal_inc ELSE 0) as december
GROUP BY
Recurrent,
Categorie
ORDER BY
Recurrent,
Categorie
Recurrent,
Categorie,
SUM(CASE WHEN betaald_in_maand = 'januari' THEN totaal_inc ELSE 0) as januari,
SUM(CASE WHEN betaald_in_maand = 'februari' THEN totaal_inc ELSE 0) as februari,
... de andere maanden ...
SUM(CASE WHEN betaald_in_maand = 'december' THEN totaal_inc ELSE 0) as december
GROUP BY
Recurrent,
Categorie
ORDER BY
Recurrent,
Categorie
Met deze uitkomst, moet je de HTML makkelijk kunnen opmaken.
Ik heb het niet getest, maar uit mijn hoofd ingetikt. Misschien moet de CASE wat anders.
Gewijzigd op 21/01/2015 14:51:29 door Jan de Laet
Alleen is er bij jou geen rekening gehouden met de splitsing tussen eenmalig en recurrent, maar dat heb ik ondertussen anders opgelost.
Er waren enkele foutjes in je query (je had het al aangegeven, maar ik heb het aangepast).
Voor het gedeelte waar 'eenmalig' moet gekozen worden:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
recurrent,
categorie,
SUM(CASE WHEN betaald_in_maand = 'januari' THEN totaal_incl ELSE 0 END) as tot_jan,
SUM(CASE WHEN betaald_in_maand = 'februari' THEN totaal_incl ELSE 0 END) as tot_feb,
SUM(CASE WHEN betaald_in_maand = 'maart' THEN totaal_incl ELSE 0 END) as tot_mrt,
SUM(CASE WHEN betaald_in_maand = 'april' THEN totaal_incl ELSE 0 END) as tot_apr,
SUM(CASE WHEN betaald_in_maand = 'mei' THEN totaal_incl ELSE 0 END) as tot_mei,
SUM(CASE WHEN betaald_in_maand = 'juni' THEN totaal_incl ELSE 0 END) as tot_jun,
SUM(CASE WHEN betaald_in_maand = 'juli' THEN totaal_incl ELSE 0 END) as tot_jul,
SUM(CASE WHEN betaald_in_maand = 'augustus' THEN totaal_incl ELSE 0 END) as tot_aug,
SUM(CASE WHEN betaald_in_maand = 'september' THEN totaal_incl ELSE 0 END) as tot_sep,
SUM(CASE WHEN betaald_in_maand = 'oktober' THEN totaal_incl ELSE 0 END) as tot_okt,
SUM(CASE WHEN betaald_in_maand = 'november' THEN totaal_incl ELSE 0 END) as tot_nov,
SUM(CASE WHEN betaald_in_maand = 'december' THEN totaal_incl ELSE 0 END) as tot_dec
FROM
accountics
WHERE
recurrent = 'nee'
GROUP BY
recurrent, categorie
ORDER BY
recurrent, categorie
recurrent,
categorie,
SUM(CASE WHEN betaald_in_maand = 'januari' THEN totaal_incl ELSE 0 END) as tot_jan,
SUM(CASE WHEN betaald_in_maand = 'februari' THEN totaal_incl ELSE 0 END) as tot_feb,
SUM(CASE WHEN betaald_in_maand = 'maart' THEN totaal_incl ELSE 0 END) as tot_mrt,
SUM(CASE WHEN betaald_in_maand = 'april' THEN totaal_incl ELSE 0 END) as tot_apr,
SUM(CASE WHEN betaald_in_maand = 'mei' THEN totaal_incl ELSE 0 END) as tot_mei,
SUM(CASE WHEN betaald_in_maand = 'juni' THEN totaal_incl ELSE 0 END) as tot_jun,
SUM(CASE WHEN betaald_in_maand = 'juli' THEN totaal_incl ELSE 0 END) as tot_jul,
SUM(CASE WHEN betaald_in_maand = 'augustus' THEN totaal_incl ELSE 0 END) as tot_aug,
SUM(CASE WHEN betaald_in_maand = 'september' THEN totaal_incl ELSE 0 END) as tot_sep,
SUM(CASE WHEN betaald_in_maand = 'oktober' THEN totaal_incl ELSE 0 END) as tot_okt,
SUM(CASE WHEN betaald_in_maand = 'november' THEN totaal_incl ELSE 0 END) as tot_nov,
SUM(CASE WHEN betaald_in_maand = 'december' THEN totaal_incl ELSE 0 END) as tot_dec
FROM
accountics
WHERE
recurrent = 'nee'
GROUP BY
recurrent, categorie
ORDER BY
recurrent, categorie
Voor het gedeelte waar 'recurrent' moet gekozen worden:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
recurrent,
categorie,
SUM(CASE WHEN betaald_in_maand = 'januari' THEN totaal_incl ELSE 0 END) as tot_jan,
SUM(CASE WHEN betaald_in_maand = 'februari' THEN totaal_incl ELSE 0 END) as tot_feb,
SUM(CASE WHEN betaald_in_maand = 'maart' THEN totaal_incl ELSE 0 END) as tot_mrt,
SUM(CASE WHEN betaald_in_maand = 'april' THEN totaal_incl ELSE 0 END) as tot_apr,
SUM(CASE WHEN betaald_in_maand = 'mei' THEN totaal_incl ELSE 0 END) as tot_mei,
SUM(CASE WHEN betaald_in_maand = 'juni' THEN totaal_incl ELSE 0 END) as tot_jun,
SUM(CASE WHEN betaald_in_maand = 'juli' THEN totaal_incl ELSE 0 END) as tot_jul,
SUM(CASE WHEN betaald_in_maand = 'augustus' THEN totaal_incl ELSE 0 END) as tot_aug,
SUM(CASE WHEN betaald_in_maand = 'september' THEN totaal_incl ELSE 0 END) as tot_sep,
SUM(CASE WHEN betaald_in_maand = 'oktober' THEN totaal_incl ELSE 0 END) as tot_okt,
SUM(CASE WHEN betaald_in_maand = 'november' THEN totaal_incl ELSE 0 END) as tot_nov,
SUM(CASE WHEN betaald_in_maand = 'december' THEN totaal_incl ELSE 0 END) as tot_dec
FROM
accountics
WHERE
recurrent = 'ja'
GROUP BY
recurrent, categorie
ORDER BY
recurrent, categorie
recurrent,
categorie,
SUM(CASE WHEN betaald_in_maand = 'januari' THEN totaal_incl ELSE 0 END) as tot_jan,
SUM(CASE WHEN betaald_in_maand = 'februari' THEN totaal_incl ELSE 0 END) as tot_feb,
SUM(CASE WHEN betaald_in_maand = 'maart' THEN totaal_incl ELSE 0 END) as tot_mrt,
SUM(CASE WHEN betaald_in_maand = 'april' THEN totaal_incl ELSE 0 END) as tot_apr,
SUM(CASE WHEN betaald_in_maand = 'mei' THEN totaal_incl ELSE 0 END) as tot_mei,
SUM(CASE WHEN betaald_in_maand = 'juni' THEN totaal_incl ELSE 0 END) as tot_jun,
SUM(CASE WHEN betaald_in_maand = 'juli' THEN totaal_incl ELSE 0 END) as tot_jul,
SUM(CASE WHEN betaald_in_maand = 'augustus' THEN totaal_incl ELSE 0 END) as tot_aug,
SUM(CASE WHEN betaald_in_maand = 'september' THEN totaal_incl ELSE 0 END) as tot_sep,
SUM(CASE WHEN betaald_in_maand = 'oktober' THEN totaal_incl ELSE 0 END) as tot_okt,
SUM(CASE WHEN betaald_in_maand = 'november' THEN totaal_incl ELSE 0 END) as tot_nov,
SUM(CASE WHEN betaald_in_maand = 'december' THEN totaal_incl ELSE 0 END) as tot_dec
FROM
accountics
WHERE
recurrent = 'ja'
GROUP BY
recurrent, categorie
ORDER BY
recurrent, categorie
Voor wat betreft je opmerking/tip: je hebt helemaal gelijk. Ik moet het ID opslaan van de categorie horende bij de lijst_accountics_categorie in de tabel accountics i.p.v. de naam. Ga ik ook nog aanpassen straks.
Toevoeging op 21/01/2015 17:34:54:
Heb je enige ervaring met javascript want dit was een van de redenen dat ik dat met de categorienaam opgelost had i.p.v. de ID. Zie topic: http://www.phphulp.nl/php/forum/topic/javascript-toont-de-velden-horende-bij-de-eerder-geselecteerde-waarden-uit-dropdown-niet/96986/
Gewijzigd op 21/01/2015 20:00:43 door Brecht S
Volgens mij zijn ze nu wel allebei hetzelfde.
Vwb je andere probleem: Ik heb daar een reactie gegeven, ik weet niet of het wel een Javascript probleem is.
Gewijzigd op 21/01/2015 18:45:24 door Jan de Laet
Gewijzigd op 21/01/2015 19:31:58 door Ger van Steenderen
@Ger: wat moet ik verstaan onder vreemd gaan?
Daar versta ik onder op twee verschillende forums dezelfde vraag stellen, maar ik speel maar een beetje met uw voeten.
@Jan, wat moet ik nader verklaren?
Maar eigenlijk wil ik wel eens weten hoe jij die 2 queries aan elkaar zou koppelen met de UNION en waarom dan niet in 1 query? Of kan dit hier niet?
Ik vermoed dat Jan dezelfde vraag had als ik... Of het gaat over de UNION...
Gewijzigd op 21/01/2015 20:17:35 door Brecht S
En ik bedoel letterlijk aan elkaar plakken, want je vervangt in jouw eerste query de ORDER BY (die daar overigens geen zin heeft) door UNION en dan kopieer en plak je de tweede query daarachter.
En het kan ook in één query zoals (ik denk dat) jij het ziet maar dan krijg je de maanden in rijen.
Heb je enige ervaring met javascript? We krijgen dat hier niet opgelost. Zie topic: http://www.phphulp.nl/php/forum/topic/javascript-toont-de-velden-horende-bij-de-eerder-geselecteerde-waarden-uit-dropdown-niet/96986/
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
series.maand_nr,
DATE_FORMAT(a.betaald_op, '%b') maand,
c.id cat_id,
c.naam categorie,
SUM(CASE a.recurring WHEN 'ja' THEN totaal_incl ELSE 0 END) recurring,
SUM(CASE a.recurring WHEN 'nee' THEN totaal_incl ELSE 0 END) eenmalig
FROM
(
SELECT
@maand_nr := @maand_nr + 1 maand_nr
FROM
information_schema.collations
CROSS JOIN
(SELECT @maand_nr := 0) u
LIMIT 12
) series
CROSS JOIN
lijst_accountics_categorie c
LEFT JOIN
accountics a
ON series.maand_nr = MONTH(a.betaald_op)
AND c.id = a.categorie AND a.betaald_op >= CAST('2015-01-01' AS DATE)
GROUP BY
series.maand_nr, maand, c.cat_id, categorie
series.maand_nr,
DATE_FORMAT(a.betaald_op, '%b') maand,
c.id cat_id,
c.naam categorie,
SUM(CASE a.recurring WHEN 'ja' THEN totaal_incl ELSE 0 END) recurring,
SUM(CASE a.recurring WHEN 'nee' THEN totaal_incl ELSE 0 END) eenmalig
FROM
(
SELECT
@maand_nr := @maand_nr + 1 maand_nr
FROM
information_schema.collations
CROSS JOIN
(SELECT @maand_nr := 0) u
LIMIT 12
) series
CROSS JOIN
lijst_accountics_categorie c
LEFT JOIN
accountics a
ON series.maand_nr = MONTH(a.betaald_op)
AND c.id = a.categorie AND a.betaald_op >= CAST('2015-01-01' AS DATE)
GROUP BY
series.maand_nr, maand, c.cat_id, categorie
Met de series subquery worden de maandnr's 1 t/m 12 gegenereerd (als alternatief voor het op het andere forum door die PG-extremist geopperde generate_series van PostGres).
Daarna een cross-join op categorien zodat alle categorieën aan alle maandnummers gekoppeld zijn.
Dan left join accountics op maand van de datum betaald en categorie.