COUNT and WHERE-clause on the same time

Hello,

I have a table (`data`) which receives his info from another application, which is structured as this:

nr    startdate          enddate        control
13    2010/03/02     2010/06/31    *some data*
13    2010/03/02     2010/06/31    *some data*
13    2010/03/02     2010/06/31    *some data*
15    2010/01/01     2010/12/31    
15    2010/01/01     2010/12/31    *some data*
15    2010/01/01     2010/12/31    *some data*
18    2010/10/01     2010/10/24  
18    2010/10/01     2010/10/24    *some data*
18    2010/10/01     2010/10/24    *some data*
...

Code (markup):

I need a query which gives the lines where the current date is between the `startdate` and the `enddate`, but also a COUNT for each line on which the column `control` isn't empty.

So if I run the needed query today, I should receive the following:

nr    startdate         enddate         number
13    2010/03/02     2010/06/31    3
15    2010/01/01     2010/12/31    2
...

Code (markup):

I tried a lot of different queries, but getting an empty result or wrong results when running it...
for example:

SELECT t.*, COUNT(t.`control`)  AS number FROM 
(SELECT * FROM `data` WHERE `control` IS NOT NULL) AS t WHERE CURDATE() BETWEEN t.`startdate` AND t.`enddate` GROUP BY t.`nr` GROUP BY `number` DESC

Code (markup):

Who can help me?

 

DROP TABLE IF EXISTS data;

CREATE TABLE data (nr INT NOT NULL DEFAULT 0,
startdate DATE NOT NULL DEFAULT '0000-00-00',
enddate DATE NOT NULL DEFAULT '0000-00-00',
control TEXT
);

INSERT INTO `data` VALUES
(13,'2010-03-02','2010-07-30','some data'),
(13,'2010-03-02','2010-08-30','some data'),
(13,'2010-03-02','2010-09-30','some data'),
(15,'2010-01-01','2010-12-31',NULL),    
(15,'2010-01-01','2010-12-31','some data'),
(15,'2010-01-01','2010-12-31','some data'),
(18,'2010-10-01','2010-10-24',NULL),  
(18,'2010-10-01','2010-10-24','some data'),
(18,'2010-10-01','2010-10-24','some data'
);


SELECT * FROM `data` NATURAL JOIN 
(SELECT nr,
SUM(IF(COALESCE(TRIM(control),'')='',0,1)) AS CNT FROM `data` 
WHERE CURDATE() BETWEEN startdate AND enddate GROUP BY 1 HAVING CNT>1) A
WHERE COALESCE(TRIM(control),'')!='';

Code (markup):

The above works when control is null or empty string:

Simplifying :

SELECT nr,
COUNT(*) AS CNT FROM `data` 
WHERE COALESCE(TRIM(control),'')!='' 
AND CURDATE() BETWEEN startdate AND enddate 
GROUP BY nr 
HAVING CNT>1;

Code (markup):

Regards

 

Thanks for your reply! I've tried your query, but getting an empty result...

Maybe I need to clarify my example a bit.
In the table (which is filled up by another application) the field nr, startdate and enddate are related. Every nr is a specific period. So all entries in the table with nr=15
have the same start- and enddate.

The field control contains a short text (which van be just one character ("x") or multiple characters, or is just empty.

What I want is a query that will give me 1 line for each period with a count of the control-field (not empty ones).

 

ok, then you've to group by All nr,startdate,enddate :

SELECT nr,
startdate,
enddate,
COUNT(*) AS CNT FROM `data` 
WHERE COALESCE(TRIM(control),'')!='' AND 
CURDATE() BETWEEN startdate AND enddate 
GROUP BY nr,startdate,enddate 
HAVING CNT>0;

Code (markup):

Next variant :

SELECT nr,
startdate,
enddate,
SUM(IF(COALESCE(TRIM(control),'')='',0,1)) AS CNT FROM `data` 
WHERE CURDATE() BETWEEN startdate AND enddate 
GROUP BY nr,startdate,enddate HAVING CNT>0;

Code (markup):

Hope it's ok now.

Edit:Edited: Everywhere should be CNT>0

 

Thanks again for your quick reply...

I've tried both queries, but the result is for every query the same... I always get an empty result...

When I run the query with only a selection on start- and enddate, I get the correct results. Also, when running a query which contains a count (but no selection for the period), I am also getting the correct results...

Combining those two will end up in getting empty results...

 

hmm, in your example data you have values for dates which are out of range, such as 2010/06/31 ( there is no 31 June ) . I got right result :

DROP TABLE IF EXISTS data;

CREATE TABLE data (nr INT NOT NULL DEFAULT 0,startdate DATE NOT NULL DEFAULT '0000-00-00',enddate DATE NOT NULL DEFAULT '0000-00-00',control TEXT);
INSERT INTO `data` VALUES
(13,'2010-03-02','2010-07-30','some data'),
(13,'2010-03-02','2010-07-30','some data'),
(13,'2010-03-02','2010-07-30','some data'),
(15,'2010-01-01','2010-12-31',NULL),    
(15,'2010-01-01','2010-12-31','some data'),
(15,'2010-01-01','2010-12-31','some data'),
(18,'2010-10-01','2010-10-24',NULL),  
(18,'2010-10-01','2010-10-24','some data'),
(18,'2010-10-01','2010-10-24','some data'
);

SELECT nr,
startdate,
enddate,
COUNT(*) AS CNT FROM `data` 
WHERE COALESCE(TRIM(control),'')!='' AND 
CURDATE() BETWEEN startdate AND enddate 
GROUP BY nr,startdate,enddate HAVING CNT>0;

Code (markup):

Result :

Please, note that I've changed input data -> enddate to 07 ( July ) for nr=13, so CURDATE() to be inside date-time range

 

You are right, there is no 31 june...
However, I've just typed some periods in here to have an example... The real recordset contains only dates which exists.

However, both queries doesn't give an empty results.

PS: Congratulations with your 300th post...

 

Oops, I wanted to say in my previous post (and cannot edit this anymore?) that both queries still give me an empty result...

 

Can you please provide the exact create table script and few lines meta-data.
Thanks