Is it possible? group by Visit.VstMobile having ( (count(Visit.VstMobile)) >1 for multiple options

Question

Let me know if this is possible in mysql query: I have referred more options in stackoverflow.

group by Visit.VstMobile having ( (count(Visit.VstMobile)>=0 and count(Visit.VstMobile)<=10) or (count(Visit.VstMobile)>=10 and count(Visit.VstMobile)<=20))

I have a range selection and multiple select like:

[0-10]

[10-20]

[20+]

I can select multiple options in above dropdown.

based on this post value I have to form the query in having clause. Please let me know if any other option.

This is my Query:

select Distinct(Visit.VstMobile) as mobile, count(VstMobile) as cnt from Visit inner join centertemp on Visit.RegistrationCentreCode=centertemp.SysNo inner join patient on Visit.VstPatCode=patient.Patcode inner join result on result.TrJobCode=Visit.VstCode inner join Test on Test.TestCode=result.TrTestCode inner join Param on Param.ParamCode=result.TrParamCode ----[some where conditions]---- group by Visit.VstMobile having ( (count(Visit.VstMobile)>=0 and count(Visit.VstMobile)<=10) or (count(Visit.VstMobile)>=10 and count(Visit.VstMobile)<=20))

-- not returning a single row.

Answer 1

Based on your fiddle, I understand that the number of ranges may vary. so the number of conditions in the having clause may vary. So the simplest option would be dynamically write the query in code, based on the user input and execute the query. here's a possible solution:

<?php
$range1 = "2-5"; //get from user 
$range2 = "5-10"; //get from user 
$range3 = ""; //get from user

$sql = "SELECT VstMobile, count(VstMobile) as cnt FROM Venkatesh group by VstMobile ";

//all ranges are optional, hence check if we need having clause
if($range1 != "" or $range2 == "" or $range3 ==""){
    $sql = $sql . " having ("

    if($range1 != ""){
        $val1 = explode('-',$range1);
        $sql = $sql . " (count(VstMobile) >=". $val1[0] . "and count(VstMobile) <= ".$val1[1].")";
    }
    if($range2 != ""){
        $val2 = explode('-',$range2);
        //check previous condition string exists before adding 'or' condition
        if($range1 != ""){
            $sql .= " or ";
        }
        $sql = $sql . " (count(VstMobile) >=". $val2[0] . "and count(VstMobile) <= ".$val2[1].")";
    }
    if($range3 != ""){
        $val3 = explode('-',$range3);
        //check previous condition string exists before adding 'or' condition
        if($range2 != "" or $range1 != ""){
            $sql .= " or ";
        }
        $sql = $sql . " (count(VstMobile) >=". $val3[0] . "and count(VstMobile) <= ".$val3[1].")";
    }
    $sql = $sql . " )"          
}

$result = $conn->query($sql);

?>

I'm not very good at php, but since you tagged php I made an attempt, so forgive syntax errors if any and I hope you understand the basic idea of it.

Edit from Drew

CREATE TABLE Venkatesh (VstMobile varchar(10));



INSERT INTO Venkatesh (`VstMobile`) VALUES ('1234567890'),('1234567890'),('1223345547'),('1223345547');
('9876543210'),('9876543210'),('1223345547'),('1223345547');
('9876543210'),('9876543210'),('9876543210'),('9876543201');
('9876543201'),('9876543201');



// select vstmobile,count(*) 
// from Venkatesh 
// group by vstmobile



SELECT VstMobile, 
count(VstMobile) as cnt 
FROM Venkatesh 
group by VstMobile 
having (
(count(VstMobile)>=0 and count(VstMobile) <=10) 

or (count(Vstmobile)>=10 and count(VstMobile)<=20) 

or (count(Vstmobile)>=20));

-- counts for all



SELECT VstMobile, 
count(VstMobile) as cnt 
FROM Venkatesh 
group by VstMobile 
having (

(count(VstMobile)>=0 and count(VstMobile) <=10) 

or (count(Vstmobile)>=20));

-- counts for all



SELECT VstMobile, 
count(VstMobile) as cnt 
FROM Venkatesh 
group by VstMobile 
having (
(count(Vstmobile)>=20));

-- 0 rows

works great what is the problem with it?