SQL Query Help

Kai

Hi

I have a table which lists the ID, usernames, Calls, and Feedback details.

i want to be able to query the Table and return the Screename, the number of Calls he took and of those calls how many had Feedback. If feedback was left then the Entry would be non empty.

so far i have :

SELECT Count(*) as NumberofRecords
FROM Out_To_Database


Which returns a count of all the calls the username has listed. but every time i try to add in another count column then i just get error messages.

Any ideas ??

logic1

Kai wrote:

Hi

I have a table which lists the ID, usernames, Calls, and Feedback details.

i want to be able to query the Table and return the Screename, the number of Calls he took and of those calls how many had Feedback. If feedback was left then the Entry would be non empty.

so far i have :

SELECT Count(*) as NumberofRecords
FROM Out_To_Database


Which returns a count of all the calls the username has listed. but every time i try to add in another count column then i just get error messages.

Any ideas ??

I assume it's MSSQL?

SELECT SCREENNAME, Count(CALLS) as CALLS_TAKEN, Count(FEEDBACK) as FEEDBACK_LEFT
FROM Out_To_Database
GROUP BY SCREENNAME;

Kinda depends what type of field "CALLS" is aswell, is it an incrementing integer or a varchar or just an int...

.logic.

Kai

Its actually Access at the minute but ill be moving it to MSSQL in a few weeks, not an option at the minute.

That works perfectly except i want to know if i can add a WHERE clause like
WHERE Supervisor = 'Some Name'

Every time i do it is giving me errors ? is there a reason for this ?

Many thanks

krinpit

Try

WHERE Supervisor LIKE 'Some Name'

Kai

well thats weird its working now after a reboot ....................... God damnit.


Ok, one more questions.

If im searching for names such as

Select Username
Where Username = 'Some Name'


How do i get around names that have a ' in them. Such as Tommy O'Toole ??

Im doing this in ASP so i dont know if using " instead of ' is possible.

krinpit

The best way to do it is to use parameters and then all you have to do is simply compare the column with the parameter value. You won't have any hassle with apostrophes

A few minutes on google will help you out there, but if you get stuck put a post up and I'll do my best

flamegrill

from being forced to use access some years ago I believe using [] around odd things can help a lot. Also a mix of "\'OToole" etc can help.

Don't forget the from part of the statement.

select id,name,boobsize from dbname where blah order by eggtimermofos.

etc.

Paul

Serbian

Kai wrote:

How do i get around names that have a ' in them. Such as Tommy O'Toole ??

Im doing this in ASP so i dont know if using " instead of ' is possible.

If you are using ASP just use something like:

Function AddSlashes(sInput)
   sInput = Replace(sInput, "'", "\'")
End Function

Dim Username, mySQL

Username = "Tommy O'Toole"
Username = AddSlashes(Username)

mySQL = "SELECT Username WHERE Username LIKE '%" & Username & "%'"

That will escape any apostraphe in the string.

Gambler

You can also use

sInput = Replace(sInput, "'", "''")

Replacing a single quote with a double quote works perfect (You can also use it in insert statements to insert Michael O'Toole into a table without getting errors.)