Newbie(?) SQL Questions.

Hobbes

Lets say I have 50 records.

1. I want to determine if any of those records has a field set with a value but I don't want to return a result set of every single record it finds (if it finds just one that's all I need to know). I also don't need the data just to know if it's set anywhere.

2. What's the command to just get a total from a SELECT. Again don't want the data just the total.

kayos

Originally posted by Hobbes
Lets say I have 50 records.

1. I want to determine if any of those records has a field set with a value but I don't want to return a result set of every single record it finds (if it finds just one that's all I need to know). I also don't need the data just to know if it's set anywhere.

This should do it for you

IF EXISTS (SELECT 1 FROM <TableName> WHERE <ColumnName> = <Value>)
BEGIN
 SELECT 'I found one'
END
ELSE
BEGIN
 SELECT 'I didnt find one'
END

Or you could just do a

SELECT COUNT(<Columname>) FROM <TableName> WHERE <ColumnName> = <Value> 

Originally posted by Hobbes

2. What's the command to just get a total from a SELECT. Again don't want the data just the total.

This should do it for you

SELECT SUM(COALESCE(<ColumnName>,0)) FROM <TableName>

If you need any more help fire the question my way.......

kayos

Hobbes

Originally posted by kayos
This should do it for you

SELECT SUM(COALESCE(<ColumnName>,0)) FROM <TableName>

[/B]

Thanks for those. That one above, will that give a total of rows? Or the total of <ColumnName>? It's total of Rows I'm looking for.

kayos

Oh sorry I took you up wrong on the second question what you want is

SELECT COUNT(<ColumnName>) FROM <TableName>

That will give you a count of the rows in the select
SUM() gives you a sum of all the values in the rows in the select

kayos

Hobbes

Thanks Ok last one (I think) how would that be returned by a resultset?

I'm guessing that I would query the field that I'm counting on?

So something like..

rs.getInt("<ColumnName>");

Is that correct?

CodeMonkey

You access the resultset like normal except there'll only be 1 result and you get the result by doing something like this...
rs.getInt("count(<ColumnName>)");
Basically use whatever you have between "select" and "from" from the query.

kayos

Hobbes just acces like a noraml column in a result set there is no need to do what CodeMonkey is saying.

You will have to give the column a name however so in your SQL just do the following

SELECT Count(<ColumnName>) as CountedColumn from .....

this will give put a name on the column for you.

kayos

bonkey

Alternately, you should be able to use rs.getInt(1) to retrieve the *first* column in the resultset, so you dont have to bother naming it.

jc

kayos

Originally posted by bonkey
Alternately, you should be able to use rs.getInt(1) to retrieve the *first* column in the resultset, so you dont have to bother naming it.

jc

tut tut I would call that magic numbers and its not a good idea. What if the column order of the resultset was to change. It's good coding practice to name your columns......

kayos