asked on

COUNT(IF( PHP MSSQL query issue

hi Experts,

Thank you for taking a look at my question! Your assistance is very much appreciated.

I am having issues with my query which works fine until I add this to it:

,COUNT(IF(RiskLevelID='4',1,null)) as RSKLVL2

The full query is here:
$query = "SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(BrandID) as counter FROM db Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '".$firstmonth."' and '".$today."' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)" ;

Can anyone spot where I have gone wrong? Thank you very much for your help!!

pritamdutt

Can please post the error you are getting and also the generated query

mavmanau

ASKER

there is no error I can see as such.

is there a way to configure it to give me a message back? this is my current code.:
$query = "SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(BrandID) as counter FROM DB Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '".$firstmonth."' and '".$today."' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)" ;
echo $query."<br />";
//Load the query
$stmt = $c->prepare($query) ;

//Run the query
$stmt->execute() ;

With using mySQL libraries in the past I have normally been able to use "or DIE ( error message) but this is first time I have been using PDO sql.

pritamdutt

can you please post the output of echo $query command

mavmanau

ASKER

SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(BrandID) as counter, COUNT(IF(RiskLevelID='2',1,null)) as RSKLVL2 FROM DB Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '2011/09/01' and '2011/09/30' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)

ASKER CERTIFIED SOLUTION

pritamdutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mavmanau

ASKER

if I alter the where statement in the query of course it will work, but the end result I want is to a total of records for each date, and then a total if the RiskLevelID = 2, and one or 2 other totals as well, but once I have one worked out the rest should follow suit.

SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(*) as counter FROM DB Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and RiskLevelID = '2' and CONVERT(VARCHAR(10),DateFunded,111) BETWEEN '2011/09/01' and '2011/09/30' GROUP BY CONVERT(VARCHAR(10),DateFunded,111) order by CONVERT(VARCHAR(10),DateFunded,111)

everything I have googled points to:
COUNT(IF(condition,value if true, value if false))

mavmanau

ASKER

in actual queries run on a server, I believe the syntax is to use CASE rather than IF.

I have used it before in other projects but that was talking to an ODBC connection or a MYSQL server using the standard MYSQL libararies rather than using the PDOSQL libraries in PHP, maybe it doesn't work using this extension.

mavmanau

ASKER

got an error finally:
PDO::errorInfo(): Array ( [0] => 42000 [1] => 156 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'if'. )

mavmanau

ASKER

All good worked it out, had to use this instead
count(case when RiskLevelID=2 then 1 else null end)

this is because of some limitations etc in PDO.

Thank you for your assistance....sometimes good just to have a sounding board!

mavmanau

ASKER

thank you for your help....I didn't use the page that you linked but you responded and got me thinking in a different way.

cheers!!