Flaw in Mysql query for making a histogram, probably in parameter definition

Question

I feel a bit annoyed in asking this, but would someone be so kind to remove the flaw in this code? My MySQL-server does not understand this query.

It is part of a larger php-mysql-javascript where the user defines how big one histogrambar needs to be. In this case the bar is 5 days. The numbers between " " are calculated $variables by the php-script before the query is presented to the database-engine.

brontabel is a table existing of two columns gendate and datum1. Gendate is the time on which the table is generated by other queries. tijd stands for the purchasing date of the good. It would bring me to far to explain why but there are more than one gendate in brontabel.

I find it important to say that I am not an IT-er. I am making this script to explain the IT-service of my company what I need. So they can than rewrite/enhance the program in a more elegant language like R or something. (In order to develop what I want from scratch they would need a lot of statistical knowledge about non-gaussian distributions)

So please give a solution that I can understand! Some of the queries in the question "MySQL: Getting data for histogram plot?" give me a brain meltdown :-)

SELECT
    gendate,
    tijd,
    SUM(CASE 
            WHEN (gendate - datum1) >= (tijd - " 5 " ) 
                 AND (gendate - datum1) < tijd 
            THEN 1 ELSE 0 END) AS aantal, 
    SUM(CASE  
            WHEN (gendate - datum1) >= tijd 
            THEN 1 ELSE 0 END)  AS rest,
    (SUM(CASE  
            WHEN (gendate - datum1) >= tijd 
            THEN 1 ELSE 0 END) * 100/count(*))  AS percentage_erna_actief 
 FROM brontabel, 
      (VALUES " 5 ", " 10 ", " 15 ", " 20 ", " 25 ", " 30 ", 
        " 35 " , " 40 ", " 45 ", " 50 ", " 55 ", " 60 ", " 65 ",
        " 70 ", " 75 ", " 80 ") AS X(tijd) 
WHERE gendate = MAX(gendate) 
GROUP BY tijd

PS Sorry for the confusion Jorge

Wat I want is to generate a bar chart (histogram) of all the items that are purchased but not yet shipped. The item will stay in the table untill it is shipped than the line is removed from the table brontabel by other queries.

If you bought the item 19-11-2013 (datum1) and the table was generated today 30-11-2013 than gendate-datum1 = 11 (item/purchase already waiting eleven days for shipping) so the bar (represented by time=15) would increment with one.

You get something like this

gendate
time (tijd)
active (how many purchases, belonging to that bar, are waiting to be shipped) rest (how many purchases wait already longer than time(tijd) to be shipped) percentage_erna_actief (rest expressed in percentage)

total amount of items/purchases waiting to be shipped = 200 (200 lines in table)

gendate    time    active    rest    percentageernaactief
30-11      5       2         198      99%  (2 purchases are less than 5 days waiting for shipping)
30-11      10      6         192      96%  (6 purchases are between 5 and 10 days waiting for shipping)
30-11      15      12        180      90%
30-11      20      20        160      80%
30-11      25      0         160      80%
30-11      30      0         160      80%
30-11      35      40        120      60%
...

Hope this makes it more clear

My answer makes your query run, but not necessarelly will give the result that you need as you don't explain it. In order to do so, you can show us your table structure (brontabel), some data example and the result that you need from that example. — Jorge Campos, Nov 30 '13 at 19:42
I typed some additional information to make it more clear. But to my understanding the proposed solution of Jorge does the trick. Thank you very much for your help. — user3052824, Dec 01 '13 at 15:19

Jorge Campos · Answer 1 · 2013-11-30T19:43:01.813

To MySql Understand what you want will need to use this:

SELECT
    gendate,
    tijd,
    SUM(CASE 
            WHEN (gendate - datum1) >= (tijd - 5 ) 
                 AND (gendate - datum1) < tijd 
            THEN 1 ELSE 0 END) AS aantal, 
    SUM(CASE  
            WHEN (gendate - datum1) >= tijd 
            THEN 1 ELSE 0 END)  AS rest,
    (SUM(CASE  
            WHEN (gendate - datum1) >= tijd 
            THEN 1 ELSE 0 END) * 100/count(*))  AS percentage_erna_actief 
 FROM brontabel, 
      (select 5 tijd union select 10 union select 15 union 
       select 20 union select 25 union select 30 union
       select 35 union select 40 union select 45 union
       select 50 union select 55 union select 60 union
       select 65 union select 70 union select 75 union
       select 80) AS X
WHERE gendate = ( select MAX(gendate) from brontabel )
GROUP BY gendate, tijd

The X table has the values that you need

And the where part is getting the max value from gendate , Also you have to group by the columns that isn't aggregated.

Also Keep in mind that you are doing a cartesian plan, which means that for every registry in brontabel table will be multiplied for the amount of values on X table

Flaw in Mysql query for making a histogram, probably in parameter definition

1 Answers1