Count subquery 1

[snowboardr]

I am trying to do a count subquery but am not very good at these...

I have two tables: privatemessage, userfolders

I am not very good with group by... and thats what error i am getting:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP By pfolder' at line 1

If you need folder explanation let me know.

SELECT 
f.fid, 
f.fuid, 
f.fname,
(SELECT COUNT(ptoid) as intFolderCount,pfolder 
FROM privatemessage 
WHERE ptoid=2 AND pfolder=f.fid) 
FROM userfolders as f WHERE fuid=2 
ORDER by fname ASC GROUP By pfolder

 

[r937]

a subquery used in the SELECT clause may return only one row and only one column

in other words, it may return only one value, and hence it is called a scalar subquery

yours looks like it's returning 2 columns

also, the GROUP BY clause isn't required here

SELECT f.fid
      , f.fuid
      , f.fname
      , ( SELECT COUNT(ptoid)  
            FROM privatemessage 
           WHERE ptoid=2 
             AND pfolder=f.fid ) as intFolderCount 
  FROM userfolders as f 
 WHERE fuid=2 
ORDER 
    by fname ASC

r937.com | rudy.ca

 

[snowboardr]

Thanks a lot, you were right it was returning two columns. It works perfect now!


-Jason