Filtering

[StateSQLboy]

I am doing a Select Count statement and I want to exclude everything in the column ending in an 'x' or an 'e'. With my limited knowledge of this stuff, I would appreciate any help.

 

[lovalles]

Select Count(field1) where right(field1,1)<>'x'

hope This helps
lovalles

 

[SQLBill]

Try this (not tested by me):

select count(mycolumn)
from mytable
where mycolumn NOT LIKE ('%e')
or mycolumn NOT LIKE ('%x')


-SQLBill

 

[StateSQLboy]

Thank You lovalles. With some slight modification your statement works perfectly!!!

 

[StateSQLboy]

I have another question. When I make my counts I also filter by weather they are funded or not funded and I input into a table where it puts either an f and the count or a n and the count into a table like this:

f 245
n 100

Now what I am trying to do is when I total them together, how can I make it look like this:

f 245
n 100
t 345

When I total them now, it puts an f inplace for the total count. Here is the sql I am using:

INSERT INTO [Licenses]
SELECT MAX(Column2) , COUNT(Column1)
FROM MYTABLE
WHERE RIGHT(Column1, 1) <> 'x' AND RIGHT(Column1, 1)
<> 'e'

 

[lovalles]

For that you need to use the Group By function, can you specify how are the fields in the Mytable table, so i can make the full t-sql string?

lovalles

 

[StateSQLboy]

What exactly do you need to know? MYTABLE is the table I am pulling the information from.

 

[lovalles]

what is the exact structure. if you type select * from mytable, what do the results look like?
lovalles

 

[StateSQLboy]

Well it lays out all the information in the table. Kinda Like this:

LastUpdate CustomerNumber CorporateID CorporateName Funded Address City State Zip LocationID

The location ID is the column I am counting, and I would love to have the results look like this:

Funded Count
T 345

 

[lovalles]

i undestand but i need to know what is the results of all this select * from mytable, i need to know what is the criteria of the group you want to make

well i just will type what i think you want.

SELECT MAX(Column2) , sum(COUNT(Column1))
FROM MYTABLE
WHERE RIGHT(Column1, 1) <> 'x' AND RIGHT(Column1, 1)
<> 'e'
group by column2,column2

 

[StateSQLboy]

The criteria I am wanting is a total count of the records minus those records in which the objects in the column end in an x or an e. Since there is no funding type for the total of the columns I would like it to put a T into the first column of the table I am making and then the total count in the next column

 

[StateSQLboy]

I am trying to count the number of location ID's a location ID looks like this A-9999-0001-A. Some Have X's and some have E's at the end. Those are to be excluded from the count. I can do all that I just need to find out how to have SQL put the letter T into a column next to the count

 

[SQLBill]

Check out the CASE statement in the BOL. As I get a chance, I'll see if I can come up with a script for you, but you might be able to solve it yourself.

-SQLBill

 

[SQLBill]

in your script you use &quot;max(column2)&quot;, which one of your columns is really the column2 that you are 'max'ing??

-SQLBill

 

[SQLBill]

You say you are selecting some information and inserting it elsewhere, correct? Have you looked at creating a DEFAULT CONSTRAINT on the new table? Set the one column with F, N, and T to have a default constraint of T. Then you select the FUNDING code of F or N and insert them, but when nothing is inserted, then a T will be inserted by default. That might be the easiest way to do what you want.

-SQLBill

 

[StateSQLboy]

SQLBILL that does seem like the easiest way to use the Default Constraint. The only problem is that when I run the query to get the total, it plugs a F into the column which I would like to hae it plug in a T instead.

 

[SQLBill]

Can you post your query? Maybe I or someone else can spot what needs fixed/added to make it work.

-SQLBill

 

[StateSQLboy]

This is my SQL:

Insert INTO [Table2]
Select MAX(Funded), Count(*)
From Table1
Where RIGHT(LocationID,1) <>'X' and RIGHT(LocationID,1) <> 'E'

And it outputs


Col1 Col2
F 345

Please Note, F is not used as part of the criteria. It just puts that value in.

 

[SQLBill]

Okay, the problem is with MAX. MAX returns the maximum of Funded. If you run:

select distinct funded
from mytable

what values do you get returned?

-SQLBill

 

[StateSQLboy]

All it gives me is a column showing weather it is F or N which is the funding type.