Selecting count of products in sql

Question

0.00/5 (No votes)

See more:

HI

I am trying to do a count of productserials from a table.

I currently have a view called listStages which is a union statement of the following tables(receive,soak,test,repair,software, dispatch)

each table in the brackets has a passfail.pass = 1 fail = 0

ListStages shows each product and if it was passed or failed at a stage.

I would like to know how would i be able to select the products which only have a pass(1) on each table and if it has a fail then it should not show in my records which are returned by the query

Posted 7-Mar-13 22:20pm

isi19

Updated 7-Mar-13 22:23pm

v2

Add a Solution

Comments

Davidduraisamy 8-Mar-13 4:24am

Restrict the condition in where clause as pass=1

isi19 8-Mar-13 4:39am

See the only thing is that the product has to be passed to be dispatched , the process will follow Rceive pass --> soak pass --> test pass --> dispatch

if there is a fail

Rceive pass--> soak fail--> repair Pass--> soak pass--> test pass--> dispatch

now i need to select only the products that follow the 1st process.
if i select * from ListStage where passfail = 1 then it will return the value of the product which had a repair. and i want it to show only the products which do not have a repair

Davidduraisamy 8-Mar-13 4:46am

hey if you are applying where condition in all table den how fail condition will come

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amol_B · Answer 1 · 2013-03-07T22:40:00

Solution 1

Hi,

Simplest way is to use pass = 1 in where condition for all tables
or use a common table expression , write your union query inside it and use where condition at output

Posted 7-Mar-13 22:40pm

Amol_B

Maciej Los · Answer 2 · 2013-03-08T05:47:00

I'm not sure wjat you want to do, but based on your comment:

Rceive pass--> soak fail--> repair Pass--> soak pass--> test pass--> dispatch
now i need to select only the products that follow the 1st process. if i select * from ListStage where passfail = 1 then it will return the value of the product which had a repair. and i want it to show only the products which do not have a repair

i recommend you to do something like this:

SQL

SELECT LS.*
FROM listStages AS LS LEFT JOIN Rceive AS RC ON LC.ProductID = RC.ProductID
WHERE RC.Pass=1

or

SQL

SELECT *
FROM listStages 
WHERE ProductID IN (
    SELECT ProductID
    FROM Rceive
    WHERE Pass=1)

P.S Vijay · Answer 3 · 2013-03-07T22:47:00

Solution 2

As per my understanding ... you want this ....

SQL

Select stage,product,SUM(CAST(passfail  AS numeric))  from listStages  
GROUP BY stage,product
Having SUM(CAST(passfail AS numeric)) = COUNT(1)

Posted 7-Mar-13 22:47pm

P.S Vijay

Updated 7-Mar-13 22:56pm

v2