[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-08
6
Medium Priority
?
286 Views
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.
0
Comment
Question by:mavmanau
  • 4
  • 2
6 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 36935771
So you don't want repeating data like there is for LP, PDP etc?

Try SELECT DISTINCT ... rest of query.
0
 

Author Comment

by:mavmanau
ID: 36935807
Hi,

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

 I shall try distinct though, thank you.
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 36936497
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?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:mavmanau
ID: 36937263
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.
0
 

Author Comment

by:mavmanau
ID: 36937481
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.
0
 

Author Closing Comment

by:mavmanau
ID: 36937739
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)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
This article discusses how to implement server side field validation and display customized error messages to the client.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month20 days, 5 hours left to enroll

872 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