SQL Query required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query required

Author

Topic

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-02-22 : 07:29:01

I have a table tblpo with fields: po(int), firstsr(int), lastsr(int).
and another table tblas with fields: serial(int), result(varchar), run_no(int).

Both the tables are related as following:
'po' is assigned to all the serials in between 'firstsr' and 'lastsr'.

Now i want a table/report to be created with the following fields:
1. 'po'

2. count of 'serial' from tblas where result='pass' and runno for that serial is max and 'serial' belongs to 'po' from tblpo.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-22 : 07:32:07

Two things required.

1. What you have tried
2. Sample data and expected output.

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-02-22 : 07:46:25

quote:
Originally posted by pk_bohra

Two things required.

1. What you have tried
2. Sample data and expected output.

I am just confused.
Here is the sample data.:
tblpo:

po=5224
firstsr=101
lastsr=150

tblas:

serial=102
result=fail
runno=1

serial=102
result=pass
runno=2

serial=103
result=pass
runno=1

Now I want a query to calculate total no of pass serial with runno=max and coressponding to po in tblpo.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-02-22 : 08:13:47

SELECT po, COUNT(S.Serial) FROM tblpo po
INNER JOIN tblas S on S.Serial BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT serial, Max(runno) mx from tblas WHERE result = 'pass'
GROUP BY serial
) A ON s.serial = A.serial AND s.runno = A.mx
GROUP BY po

Vaibhav T

If I cant go back, I want to go fast...

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-02-23 : 02:18:27

quote:
Originally posted by vaibhavktiwari83

SELECT po, COUNT(S.Serial) FROM tblpo po
INNER JOIN tblas S on S.Serial BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT serial, Max(runno) mx from tblas WHERE result = 'pass'
GROUP BY serial
) A ON s.serial = A.serial AND s.runno = A.mx
GROUP BY po

Vaibhav T

If I cant go back, I want to go fast...

Though I am not able to understand this, still it gives result.
Thanks

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-03-04 : 07:12:07

quote:
Originally posted by vaibhavktiwari83

SELECT po, COUNT(S.Serial) FROM tblpo po
INNER JOIN tblas S on S.Serial BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT serial, Max(runno) mx from tblas WHERE result = 'pass'
GROUP BY serial
) A ON s.serial = A.serial AND s.runno = A.mx
GROUP BY po

Vaibhav T

If I cant go back, I want to go fast...

Can I display another column "qty" from tblpo with above query. I tried this but it shows some error:

SELECT productiono,qty,COUNT(distinct S.Serialnumber) as quantity FROM tblpo as po
INNER JOIN tblas S on S.Serialnumber BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT Serialnumber, Max(runno) mx from tblas WHERE (result = 'pass' and process='fpt')
GROUP BY Serialnumber
) A ON s.Serialnumber = A.Serialnumber AND s.runno = A.mx
GROUP BY productiono

Error is:
Msg 8120, Level 16, State 1, Line 1
Column 'tblpo.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-04 : 07:21:42

quote:
Originally posted by rohit04413

quote:
Originally posted by vaibhavktiwari83

SELECT po, COUNT(S.Serial) FROM tblpo po
INNER JOIN tblas S on S.Serial BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT serial, Max(runno) mx from tblas WHERE result = 'pass'
GROUP BY serial
) A ON s.serial = A.serial AND s.runno = A.mx
GROUP BY po

Vaibhav T

If I cant go back, I want to go fast...

Can I display another column "qty" from tblpo with above query. I tried this but it shows some error:

SELECT productiono,qty,COUNT(distinct S.Serialnumber) as quantity FROM tblpo as po
INNER JOIN tblas S on S.Serialnumber BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT Serialnumber, Max(runno) mx from tblas WHERE (result = 'pass' and process='fpt')
GROUP BY Serialnumber
) A ON s.Serialnumber = A.Serialnumber AND s.runno = A.mx
GROUP BY productiono, qty

Error is:
Msg 8120, Level 16, State 1, Line 1
Column 'tblpo.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

See the red part...

Vaibhav T

If I cant go back, I want to go fast...

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-03-07 : 03:46:10

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by rohit04413

quote:
Originally posted by vaibhavktiwari83

SELECT po, COUNT(S.Serial) FROM tblpo po
INNER JOIN tblas S on S.Serial BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT serial, Max(runno) mx from tblas WHERE result = 'pass'
GROUP BY serial
) A ON s.serial = A.serial AND s.runno = A.mx
GROUP BY po

Vaibhav T

If I cant go back, I want to go fast...

Can I display another column "qty" from tblpo with above query. I tried this but it shows some error:

SELECT productiono,qty,COUNT(distinct S.Serialnumber) as quantity FROM tblpo as po
INNER JOIN tblas S on S.Serialnumber BETWEEN po.firstsr AND po.lastsr
INNER JOIN
(
SELECT Serialnumber, Max(runno) mx from tblas WHERE (result = 'pass' and process='fpt')
GROUP BY Serialnumber
) A ON s.Serialnumber = A.Serialnumber AND s.runno = A.mx
GROUP BY productiono, qty

Error is:
Msg 8120, Level 16, State 1, Line 1
Column 'tblpo.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

See the red part...

Vaibhav T

If I cant go back, I want to go fast...

In the same query, How can i display another column in the result which should show similar records but with result='fail'

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2011-03-10 : 23:38:56

Anyone Plz Help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources