Barand

In the same way that the query tests for a time period of six months, a similar test could added in the same statement to check for a minimum period

Your original post required records for each person/date so you have decide what to do about those occasions where a patient has several tests for the same condition in a single day. At the moment it takes the MIN value but you could also have MAX or AVG. Whichever one you decide, you still will have a different count between the two tables because of this date repetition.

I think I know why there are missing records. Yesterday we had an instance where a patient had six tests on the same day. When we create mastertot2 we are grouping by CodicePz , DataRIchiesta so those six records are condensed to one.

It looks like you just need to run a simple query SELECT C FROM table1 WHERE A = $input1 AND B = $input2 Add an INDEX on (A,B) to the table

Have you any NULL results? SELECT COUNT(*) FROM mastertot WHERE risultato IS NULL;

One of the problems with storing descriptions instead of foreign keys in tables is they are prone to misspelling. One check you could do is SELECT DescrPrestaz , COUNT(*) as sampleTotal FROM mastertot GROUP BY DescrPrestaz; This will give the counts for each and will show if there are descriptions that are wrong

Null values are ignored in aggregation functions (COUNT, SUM etc) so SELECT COUNT(a1) as count1 , COUNT(a2) as count2 , COUNT(a3) as count3 FROM tablename

"codicepz" is unique on its own, you don't need the the combination of Nome/codicepz.to count them SELECT `Cognome` , COUNT(distinct `CodicePz`) as `Count` , GROUP_CONCAT(distinct `Nome`, `CodicePz` ORDER BY `Nome` SEPARATOR ' , ') as Names FROM mastertot_head10000 GROUP BY `Cognome` You don't need all those backticks. They are needed only when the identifier conflicts with a reserved word (unlikely in your tables) or if it contains a space or other special characters.

Good decision, Admins. A welcome addition to the ranks. Congratulations QOC.

Have a look at INSERT ... ON DUPLICATE KEY UPDATE ... statements http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Why don't you just store their date of birth? Next year a user whose record now has 650000 will be 66

IN ('') will look for those with an empty string. If you do not have any values in $tier then omit that condition from the query. See reply #2

echo $sql; and see what you have

Using LIKE '%BMW'in a where clause will prevent the query from using an index and will be slow. The IN with join() that you are using will only work with numeric values - strings need to be enclosed in quotes. So you can have IN (1,2,3) but not IN (BMW,Audi). The latter would have to be IN ('BMW', 'Audi') Therefore the code is "WHERE manufacturer IN ('". implode("','", $checklist) . "')"; Don't use POST values directly in queries - sanitize first or use prepared statements

Put it with the existing WHERE WHERE descrprestaz = 'Creatinina' AND eta = 650000 650000 ??? - how are you storing eta (age?) ?

Yes. Each record in a run of results meeting the criteria is allocated the number of that group. When the run ends the group number is incremented and continues being incremented until we hit another group. We then GROUP BY the group number (@N) to get the group count and concatenate the records in the group

Moderation of first-time posts perhaps?

I forgot to initialize the @startdate variable Change SELECT @N:=0, @prevres:=NULL, @prevuser:=NULL to SELECT @startdate:=NULL, @N:=0, @prevres:=NULL, @prevuser:=NULL

I was using @N to count the records in each group but realized that I could do that without the @N However I have now put it back as an incrementing group identifier so I am not relying on the dates. This should cure the "six-in-one-day" problem SELECT User , COUNT(*) as `Count` , GROUP_CONCAT(datarichiesta,' (',result,')' ORDER BY datarichiesta SEPARATOR ' , ') as Dates FROM ( SELECT datarichiesta , @N := IF((codicepz<>@prevuser) OR (@prevres < 10) OR (risultato < 10) OR (datarichiesta > @startdate + INTERVAL 6 MONTH) , @N+1, @N) as groupno , @startdate := IF((codicepz<>@prevuser) OR (@prevres < 10) OR (risultato < 10) OR (datarichiesta > @startdate + INTERVAL 6 MONTH) , datarichiesta, @startdate) as startdate , @prevres:=risultato as result , @prevuser:=codicepz as user FROM mastertot JOIN ( SELECT @N:=0, @prevres:=NULL, @prevuser:=NULL ) as init WHERE descrprestaz = 'a1' ORDER BY codicepz, datarichiesta ) as detail GROUP BY user,groupno HAVING `Count` >= 5;

It happened because all six readings were taken on the same date and so there is a group of records with the same startdate and a count > 5. Now to find a cure

Sorry. I created a test user with a 6 month gap (id 200) to test the query. You can take that line out of the query

So do I take it that your second query (profits) was being run within a loop of query for each user, hence the "WHERE id = :id" ? If so , that is a really inefficient way to do it. You should use JOINS rather than running queries inside loops. I am assuming you want to rank by profit. To get the rank, just maintain a counter variable as you process the results SELECT u.username ,SUM(price) as price ,SUM(costs) as costs ,SUM(cargodamage) as cargodamage ,SUM(price - costs - cargodamage) as profit FROM drive_routes dr INNER JOIN users u ON u.id = dr.driver GROUP BY driver ORDER BY profit DESC;

This will start a new date group if it finds a gap of greater than six months SELECT User , COUNT(*) as `Count` , GROUP_CONCAT(accessdate,' (',result,')' ORDER BY accessdate SEPARATOR ' , ') as Dates FROM ( SELECT accessdate , @startdate := IF((usercode<>@prevuser) OR (@prevres < 10) OR (analysisresult < 10) OR (accessdate > @startdate + INTERVAL 6 MONTH) , accessdate, @startdate) as startdate , @prevres:=analysisresult as result , @prevuser:=usercode as user FROM result JOIN ( SELECT @startdate:=NULL, @prevres:=NULL, @prevuser:=NULL ) as init WHERE analysisname = 'a1' AND usercode = 200 ORDER BY usercode, accessdate ) as detail GROUP BY user,startdate HAVING `Count` >= 5

Yes. You would check the accessdate against the @startdate and reset @N to 1 if the gap was more than 6 months

The inner subquery selects the "a1" records in user/date order then reads through them. Where there is a run of records with a result of 10 or more they are given the same "startdate" (the first date in the run) The outer query reads these results and GROUPS BY startdate, outputting those where there is a run count of 5 or more. It also GROUP_CONCATenates the dates and result values for each record in the group The easiest way is probably to show you the GROUP_CONCAT output so you can see what it does | User | Count | Dates [ GROUP_CONCAT() date and (result value) ] | +------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 6 | 2008-02-04 (17) , 2008-02-05 (14) , 2008-02-06 (17) , 2008-02-07 (18) , 2008-02-08 (12) , 2008-02-09 (18) | | 1 | 8 | 2008-03-10 (24) , 2008-03-11 (12) , 2008-03-12 (12) , 2008-03-13 (16) , 2008-03-14 (20) , 2008-03-15 (17) , 2008-03-16 (25) , 2008-03-17 (13) | | 1 | 6 | 2008-03-25 (10) , 2008-03-26 (15) , 2008-03-27 (12) , 2008-03-08 (21) , 2008-03-29 (10) , 2008-03-30 (23) | | 1 | 6 | 2008-04-25 (23) , 2008-04-26 (22) , 2008-04-27 (21) , 2008-04-28 (12) , 2008-04-29 (14) , 2008-04-30 (22) | | 1 | 5 | 2008-05-07 (25) , 2008-05-08 (25) , 2008-05-09 (25) , 2008-05-10 (16) , 2008-05-11 (20) | +------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+