Query Help

[MickDub]

I have follwoing table showing part number M1 with 5 entries
PONum PartNum
5331 M1
5331 M1
5332 M1
5332 M1
5332 M1

what i need help with is to be able to write a query that return that there are 5 entries for M1 and that its on two seperate PoNum's

Part Num PartCount PoNumCount
M1 5 2 '(showings it is on 2 Different PoNumbers)

 

[lespaul]

SELCT PartNum, COUNT(*), COUNT(PONum) FROM TableName
GROUP BY PartNum

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[MickDub]

Thansk For Help Coco

The first method gives me an error
"cannot find table or query 'select distinct partnum,ponum
from tablenum'. make sure it exist

the secound method using two queries
Gives me the "PoNum" count but left out in my question that also need the PartNum count sorry about this

have changed Query to what it should have read below


I have follwoing table showing part number M1 with 5 entries
PONum PartNum
5331 M1
5331 M1
5332 M1
5332 M1
5332 M1

what i need help with is to be able to write a query that return that there are 5 entries for M1 and that its on two seperate PoNum's

Part Num PartCount PoNumCount
M1 5 2 '(showings it is on 2 Different PoNumbers)

 

[MickDub]

Cheers for help coco
got working by chainging 2 query option method a little

Query1
SELECT DISTINCT partnum, ponum, count(partnum) AS pocount
FROM PoMov GROUP BY partnum, ponum;

Query2
SELECT partnum, count(PONum) AS PoNumbCount,sum(Pocount)
FROM query1
GROUP BY partnum;

Super stuff thanks again