Re: SQL select basics

[Moon's Father]

select * from
(
SELECT ID_number, count( CU_number ) AS CC

FROM MyTable

GROUP BY ID_number
) T
WHERE CC =
(
select max(cc) from
(
SELECT ID_number, count( CU_number ) AS CC

FROM MyTable

GROUP BY ID_number
) T2
)


On Wed, Oct 15, 2008 at 5:08 PM, dave aptiva [EMAIL PROTECTED]wrote:

 Hello all,

 I'm new to sql and have a question if someone would be kind enough to help
 me with, if I have a table that stores telemarketers by ID_number and the
 customer that they spoke to by CU_number and I use a select statement such
 as;



 SELECT ID_number, count( CU_number )

 FROM MyTable

 GROUP BY ID_number;



 To find the number of calls that each telemarketer made, this works fine
 but
 how do I then use the results that are returned to find those telemarketers
 that made the most telephone calls ?



 I tried SELECT ID_number, max( count( CU_number ) ) but this causes an
 error
 # - Invalid use of group function 



 I also tried



 SELECT ID_number, count( CU_number ) AS CC

 FROM MyTable

 GROUP BY ID_number

 WHERE CC = max( CC );



 But this also causes an error, anyone able to offer some pointers ?



 Thanks Dave.




-- 
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

SQL select basics

[dave aptiva]

Hello all, 

I'm new to sql and have a question if someone would be kind enough to help
me with, if I have a table that stores telemarketers by ID_number and the
customer that they spoke to by CU_number and I use a select statement such
as; 

 

SELECT ID_number, count( CU_number )

FROM MyTable

GROUP BY ID_number;

 

To find the number of calls that each telemarketer made, this works fine but
how do I then use the results that are returned to find those telemarketers
that made the most telephone calls ? 

 

I tried SELECT ID_number, max( count( CU_number ) ) but this causes an error
# - Invalid use of group function  

 

I also tried 

 

SELECT ID_number, count( CU_number ) AS CC

FROM MyTable

GROUP BY ID_number

WHERE CC = max( CC );

 

But this also causes an error, anyone able to offer some pointers am I able
to use sub queries ?

 

Thanks Dave.

 

Re: SQL select basics

[Ian Christian]

2008/10/17 dave aptiva [EMAIL PROTECTED]:

 I tried SELECT ID_number, max( count( CU_number ) ) but this causes an error
 # - Invalid use of group function 


# sqlite3
SQLite version 3.5.9
Enter .help for instructions
sqlite create table moo (id_number, cu_number);
sqlite insert into moo(1, 1);
SQL error: near 1: syntax error
sqlite insert into moo values (1, 1);
sqlite insert into moo values (1, 2);
sqlite insert into moo values (1, 3);
sqlite insert into moo values (2, 3);
sqlite SELECT id_number, count(cu_number) FROM moo GROUP BY id_number;
1|3
2|1
sqlite SELECT id_number, count(cu_number) FROM moo GROUP BY id_number
HAVING count(cu_number) = max(cu_number);
1|3



OR:

sqlite SELECT id_number, count(cu_number) FROM moo GROUP BY id_number
ORDER BY count(cu_number) DESC LIMIT 1;
1|3

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL select basics

[dave aptiva]

Hello all, 

I'm new to sql and have a question if someone would be kind enough to help
me with, if I have a table that stores telemarketers by ID_number and the
customer that they spoke to by CU_number and I use a select statement such
as; 

 

SELECT ID_number, count( CU_number )

FROM MyTable

GROUP BY ID_number;

 

To find the number of calls that each telemarketer made, this works fine but
how do I then use the results that are returned to find those telemarketers
that made the most telephone calls ? 

 

I tried SELECT ID_number, max( count( CU_number ) ) but this causes an error
# - Invalid use of group function  

 

I also tried 

 

SELECT ID_number, count( CU_number ) AS CC

FROM MyTable

GROUP BY ID_number

WHERE CC = max( CC );

 

But this also causes an error, anyone able to offer some pointers ?

 

Thanks Dave.

Re: SQL select basics

[Jim Lyons]

SELECT ID_number, count( CU_number )
FROM MyTable
GROUP BY ID_number
order by 2 desc
limit 10

will give you the top 10.  Change 10 to whatever you want or take off the
limit clause to get all records.

On Fri, Oct 17, 2008 at 4:25 AM, dave aptiva [EMAIL PROTECTED]wrote:

 Hello all,

 I'm new to sql and have a question if someone would be kind enough to help
 me with, if I have a table that stores telemarketers by ID_number and the
 customer that they spoke to by CU_number and I use a select statement such
 as;



 SELECT ID_number, count( CU_number )

 FROM MyTable

 GROUP BY ID_number;



 To find the number of calls that each telemarketer made, this works fine
 but
 how do I then use the results that are returned to find those telemarketers
 that made the most telephone calls ?



 I tried SELECT ID_number, max( count( CU_number ) ) but this causes an
 error
 # - Invalid use of group function 



 I also tried



 SELECT ID_number, count( CU_number ) AS CC

 FROM MyTable

 GROUP BY ID_number

 WHERE CC = max( CC );



 But this also causes an error, anyone able to offer some pointers am I able
 to use sub queries ?



 Thanks Dave.










-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com