58105

How to count consecutive number of 10 days

Question:

I have table with columns: id, name, date, present Column present have values 0 or 1 or 2 and ... more I need to count how many 0 valous is in current month 2013-07-01 - 2013-07-31 but count only when there are or more than 10 times.

for example if i have 2013-07-01 to 2013-07-10 valoues 0 it should count it and let me know that is 10 or more consecutives days like 11, 12 or more, but if it was less than 10 should count nothing.

I was trying some examples from stack... but they are different problems... so i need little help with that mysql query.

i have smth like this but need consecutives 10 days like >= 10

$sql = mysql_query("SELECT COUNT(name) as count FROM `table` WHERE (`present` = 0) AND (`date` BETWEEN '2013-07-01' AND '2013-07-31')"); while($row = mysql_fetch_array($sql)){ $result = $row['count']; }

It counts me every 0 values in date between 2013-07-01 and 2013-07-31 but i need count how many days start from 10 or more consecutives days

column present have 0 and other numbers like 1, 2, 3... so i need count only 0 with 10 or more consecutives days

here is SqlFiddle i was trying to make warking from answer <a href="http://sqlfiddle.com/#!2/1bde8/2" rel="nofollow">http://sqlfiddle.com/#!2/1bde8/2</a>

best regards m.

Answer1:

This approach uses correlated subqueries to calculate two values.

The first value is the date of the previous record where Present = 1. This allows you to get the number of days in a row where Present = 0 by using datediff().

The second is the Present value of tomorrow, which will be NULL on the last day of the month. When today has Present = 0 and tomorrow is either 1 or NULL, then we can use this record. It is the end of a sequence of 0s.

From there is it just a question of adding up the values according to the conditions that you set. The following query assumes that you want to do this for each name:

select name, sum(case when datediff(date, lastPresentDate) >= 10 then datediff(date, lastPresentDate) else 0 end) as DaysCounted from (select t.*, (select coalesce(max(date), '2013-06-30') from t t2 where t2.name = t.name and t2.present <> 0 and t2.date <= t.date and t2.date between '2013-07-01' and '2013-07-31' ) as lastPresentDate, (select t2.present from t t2 where t2.name = t.name and t2.date = adddate(t.date, 1) order by t2.date limit 1 ) as TomorrowPresent from t where date between '2013-07-01' and '2013-07-31' ) t where Present = 0 and (TomorrowPresent = 1 and TomorrowPresent is null) group by name

Answer2:

This query should give you a count only when it is 10 or greater than 10.

SELECT COUNT(`name`) as `count` FROM `table` WHERE (`present` = 0) AND (`date` BETWEEN '2013-07-01' AND '2013-07-31') HAVING `count` >= 10;

Hope it helps!

Answer3:

Not tested, but you could use user variables like this:-

SELECT SUM(if(ConsCounter=10, 1, 0)) FROM ( SELECT id, name, date, present, @Counter := IF(@PrevPresent = present AND present = 0, @Counter + 1, 0) AS ConsCounter, @PrevPresent = present FROM ( SELECT id, name, date, present FROM `table` ORDER BY date ) Sub1 CROSS JOIN (SELECT @PrevPresent:=-99999, @Counter:=0) Sub2 ) Sub4

Get all the records in date order and add a sequence number for the count since the present was first 0. Then count the number of times that counter is 10.

Recommend

  • Time series prediction / forecast with TBATS failing with 'Error in tau + 1 + adj.beta + object
  • WHERE If condition is not met get all values including NULL
  • Get count of zeros in an integer using MySQL
  • Get count of row and sum group by quarter of date, if another column doesnt exits a value in SQL Ser
  • Replacing NULL value in current row based on 'closest' matching 'earlier' row. (
  • Birt script behaves differently via web viewer
  • In Jquery AND Rails, how to get visible character count of a string?
  • How do I tabulate across an entire R data frame?
  • Density Value for each Return
  • Dimension issue with scipy's curve_fit function
  • How can I convert this tuple of tuples into a count of its elements?
  • What's included in the “Initialization time” in the Symfony2 web profiler?
  • Get highest value from a file using mSL and mIRC
  • How to override JAXB @XMLAccessorType(XMLAccessType.FIELD) specified at a Class level with @XMLEleme
  • Facebook Open Graph Story Custom Actions Keep Getting Rejected - Advice Please?
  • finding symmetric difference/unique elements in multiple arrays in javascript
  • Using Generics on right hand side in Java 6?
  • How to use function wrapper in mustache.php?
  • MySQL Order by column = x, column asc?
  • Groovy: Unexpected token “:”
  • Replace value with Factor in r data.table
  • Django: Count of Group Elements
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Splitting given String into two variables - php
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • Build own AppleScript numerical error handling
  • How to get next/previous record number?
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • python regex in pyparsing
  • How do you join a server to an Active Directory (domain)?
  • Android Google Maps API OnLocationChanged only called once
  • How does Linux kernel interrupt the application?
  • Can't mass-assign protected attributes when import data from csv file
  • Unable to use reactive element in my shiny app
  • How can I use threading to 'tick' a timer to be accessed by other threads?