SQL help....

[pythonjosh]

Can anyone tell me why this wouldn't work? (I left out the rest, it's getting an Invalid number of arguments error)

select pvid,count(distinct ptid,d_visit)

If this is fine for Oracle 9i, I will post up the rest of the query. Just want to keep things simple.

[Joe_Camel]

Not sure about Oracle, but it works fine in mysql as long as you group the results by one of those columns.

[pythonjosh]

It works for my MySQL too. Oracle 9i is pretty ancient. fvckin sh!t

I know I can run a subquery like

select distinct ptid,d_visit from blah blah blag

How can I count() a subquery?

[kernel]

what you wrote in your first one honestly doesn't make any sense....I think what you need is:

SELECT PVID, COUNT(*)

FROM (
SELECT DISTINCT PVID, D_VISIT
FROM TBL A) B

GROUP BY PVID

[pythonjosh]

That query doesn't work Kernel.
count(distinct ptid,d_visit) is exactly what I need, but it seems Oracle 9i is too old or something.

[kernel]

by doesn't work, you mean won't run (in which case, what's the error?) or doesn't give you what you want?

Are you wanting a count of the unique ptid and d_visits? If not, write out what you want in english instead of SQL and i'll see if I can help.

If you do want a count of the unique ptid and d_visits, then you just need this:
SELECT COUNT(*)

FROM (
SELECT DISTINCT PVID, D_VISIT
FROM TBL A) B

[pythonjosh]

How do I do a count of distinct ptid,d_visit per each distinct pvid? And displaying like 'SELECT PVID,COUNT(*)"

[kernel]

oh, sorry, didn't notice there is a pvid and a ptid.

it's hard to do it without seeing the data, but can you not just do a distinct pvid, ptid, d_visit and then a count of that? Basically add ptid into the last subquery I have above?

[pythonjosh]

I think I should just extract the data, and then import it into my MySQL db so that the original query would work. I hope we upgrade to 10g
Using PHP to automate the process.