counting rows in a table

Question

Post reply

counting rows in a table

schauhan13

SSC Eights!

Points: 901
More actions
December 9, 2009 at 12:37 pm

#218625

i have two tables. One is Parts( partnbr, desc,qty)
Second is ExclusionList(partnbr,desc)
Now i want to select rows from parts table excluding part nbrs which are present in exclusionlist table.
Something like this:-
select * from parts where partnbr <> (select partnbr from exclusionlist)
I dont know how to do that can anyone help me with this?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Your thread name indicates counting rows in table and you say in your reply, select rows.

Any way.

To Count rows

SELECT COUNT(*) FROM parts where partnbr not in

(SELECT DISTINCT partnbr FROM ExclusionList)

To display all rows

SELECT * FROM parts where partnbr not in (SELECT DISTINCT partnbr FROM ExclusionList)

If you only need top display the partnbr,

SELECT partnbr FROM parts

EXCEPT

SELECT partnbr FROM ExclusionList

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

schauhan13 SSC Eights! Points: 901 More actions · Answer 2

Solution:-

SELECT COUNT(*) AS Expr1

FROM (SELECT partnbr

FROM Parts

WHERE (partnbr NOT IN

(SELECT partbr

FROM exclusionlist))) DERIVEDTBL

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

schauhan13 (12/9/2009)
Solution:-
SELECT COUNT(*) AS Expr1
FROM (SELECT partnbr
FROM Parts
WHERE (partnbr NOT IN
(SELECT partbr
FROM exclusionlist))) DERIVEDTBL

What big difference does it have with the first SQL Code given by me?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Bru Medishetty (12/9/2009)
schauhan13 (12/9/2009)
Solution:-
SELECT COUNT(*) AS Expr1
FROM (SELECT partnbr
FROM Parts
WHERE (partnbr NOT IN
(SELECT partbr
FROM exclusionlist))) DERIVEDTBL
What big difference does it have with the first SQL Code given by me?

Heh... nothing, except... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rufio Hall of Fame Points: 3064 More actions · Answer 5

How 'bout:

;with

ValidParts(partnbr) as(

select partnbr from Parts

except

select partnbr from ExclusionList)

select count(1) ReportedParts from ValidParts