asked on

SQL query in PHP - What is wrong my my query?

Hello Experts!

Thank you for taking the time to look at my question. Essentially I have the below query that is not working, and I can't see why.

SELECT BrandID,COUNT(CONVERT(VARCHAR(10),DateFunded,111)) as CNT,RiskLevelID count((if(BrandID==2,0)) 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 CNT order by CONVERT(VARCHAR(10),DateFunded,111)

Essentially what I want to do, is given the data, I want it to group the data by the different dates and count it, - do I need to remove some of the other columns in order for that to happen?

This Query works fine, which gives me the sample date that follows after the query.

SELECT BrandID,CONVERT(VARCHAR(10),DateFunded,111),RiskLevelID 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' order by CONVERT(VARCHAR(10),DateFunded,111)

Brand ID Date Funded Risk Level ID
PDP 2011/09/01 4
NZGC 2011/09/01 3
PDP 2011/09/02
NZGC 2011/09/02 3
LP 2011/09/02 1
LP 2011/09/02 1
FSM 2011/09/03 3
PDP 2011/09/03 3
NSW 2011/09/03 3

Thank you very much for your assistance! it is much appreciated.

Norie

So you don't want repeating data like there is for LP, PDP etc?

Try SELECT DISTINCT ... rest of query.

mavmanau

ASKER

Hi,

Basically I want it to count the amount of rows that have each different date in.

I shall try distinct though, thank you.

ASKER CERTIFIED SOLUTION

Norie

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mavmanau

ASKER

With the query that doesn't work, nothing comes up at all.

I would like to do get a count of the various brand ID's on the dates. Also a total count of all rows on a each date.

The second query gives the results I posted. Just thought I would start with it giving me a count for each date and move from there.

mavmanau

ASKER

The Following works now:

$query = "SELECT CONVERT(VARCHAR(10),DateFunded,111), COUNT(BrandID) FROM [Laps].[dbo].[Loan] 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)" ;

But it now falls over if I try to add this:
, COUNT(IF(RiskLevelID=2,1,0) as RSKLVL2

Essentially I want it to count if the RiskLevelID = 2

can you see anything wrong in the syntax? once I have this one, I am all good.

mavmanau

ASKER

thank you for your help. I ended up working out it was due to PDO compatability and stuff. This below line worked!!!

count(case when RiskLevelID=2 then 1 else null end)