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

Posted on 2011-10-08
Last Modified: 2012-05-12
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.
Question by:mavmanau
    LVL 33

    Expert Comment

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

    Try SELECT DISTINCT ... rest of query.

    Author Comment


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

     I shall try distinct though, thank you.
    LVL 33

    Accepted Solution

    Apologies, I misread your post.

    Do you want to get counts for each brand for each date?

    Which of the posted queries gives the results you posted?

    What results do you get with the query that doesn't work?

    Author Comment

    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.

    Author Comment

    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.

    Author Closing Comment

    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)

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    The viewer will learn how to count occurrences of each item in an array.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now