dbTalk Databases Forums  

Request modelling

Go Back dbTalk Databases Forums Databases mailing.database.mysql Request modelling

mailing.database.mysql mailing.database.mysql


Discuss Request modelling in the mailing.database.mysql forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Nils Valentin
 
Posts: n/a

Default Request modelling - 07-11-2003 , 08:01 PM





Hi MySQL Fans ;-)

I would like to list all courses with have less than lets say 10 participants.
Does anybody know how I would have to modify the code below to get this to
work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually within the
WHERE (clause which is not allowed).

I would appreciate any help or workaround.

--
---
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw


Reply With Quote
  #2  
Old   
John Coder
 
Posts: n/a

Default Re: Request modelling - 07-11-2003 , 08:07 PM





On Fri, 2003-07-11 at 13:41, Nils Valentin wrote:
Quote:
Hi MySQL Fans ;-)

I would like to list all courses with have less than lets say 10 participants.
Does anybody know how I would have to modify the code below to get this to
work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually within the
WHERE (clause which is not allowed).
Try
SELECT schedule_id,count(schedule_id) as attendees FROM attendance WHERE
antendees<10 GROUP BY schedule_id ;

Johnn Coder



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw



Reply With Quote
  #3  
Old   
Bruce Feist
 
Posts: n/a

Default Re: Request modelling - 07-11-2003 , 08:23 PM


Nils Valentin wrote:

Quote:
I would like to list all courses with have less than lets say 10 participants.
Does anybody know how I would have to modify the code below to get this to
work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually within the
WHERE (clause which is not allowed).
That's precisely why HAVING exists.

SELECT schedule_id,count(schedule_id)
FROM attendance
HAVING count(schedule_id) <10
GROUP BY schedule_id ;

Bruce Feist



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw



Reply With Quote
  #4  
Old   
Nils Valentin
 
Posts: n/a

Default Re: Request modelling - 07-11-2003 , 08:24 PM


Hi John,

Thank you for the reponse. I tried that (I didnt post it here), but that gi=
ves=20
me the error

mysql> SELECT schedule_id,count(schedule_id) AS ct FROM attendance WHERE ct=
<10=20
GROUP BY schedule_id ;
ERROR 1054: Unknown column 'ct' in 'where clause'

Best regards

Nils Valentin
Tokyo/Japan


2003=E5=B9=B4 7=E6=9C=88 12=E6=97=A5 =E5=9C=9F=E6=9B=9C=E6=97=A5 10:03=E3=
=80=81John Coder =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=BE=E3=
=81=97=E3=81=9F:
Quote:
On Fri, 2003-07-11 at 13:41, Nils Valentin wrote:
Hi MySQL Fans ;-)

I would like to list all courses with have less than lets say 10
participants. Does anybody know how I would have to modify the code bel=
ow
to get this to work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually with=
in
the WHERE (clause which is not allowed).

Try
SELECT schedule_id,count(schedule_id) as attendees FROM attendance WHERE
antendees<10 GROUP BY schedule_id ;

Johnn Coder
=2D-=20
=2D--
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3...ie.nctu.edu.tw



Reply With Quote
  #5  
Old   
Nils Valentin
 
Posts: n/a

Default Re: Request modelling - 07-11-2003 , 09:18 PM


2003=E5=B9=B4 7=E6=9C=88 12=E6=97=A5 =E5=9C=9F=E6=9B=9C=E6=97=A5 10:55=E3=
=80=81Nils Valentin =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=BE=
=E3=81=97=E3=81=9F:
Quote:
Hi Bruce;

Thank you for the reply.

I passed the core exam but obviously its not enough ;-) Live and learn ;-)
Aeeh thats was supposed to be ... life and learn..
Perhaps somebody knows a good spellchecker for logical mistakes ?? ;-)
That would have immediately popped up on my screen ;-)
=20

Best regards

Nils Valentin
Tokyo/Japan


Quote:
Thank you very much for the reply.

Best regards

Nils Valentin

2003=E5=B9=B4 7=E6=9C=88 12=E6=97=A5 =E5=9C=9F=E6=9B=9C=E6=97=A5 10:20=E3=
=80=81Bruce Feist =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=BE=E3=
=81=97=E3=81=9F:
Quote:
Nils Valentin wrote:
I would like to list all courses with have less than lets say 10
participants. Does anybody know how I would have to modify the code
below to get this to work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually
within the WHERE (clause which is not allowed).

That's precisely why HAVING exists.

SELECT schedule_id,count(schedule_id)
FROM attendance
HAVING count(schedule_id) <10
GROUP BY schedule_id ;

Bruce Feist
=2D-=20
=2D--
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3...ie.nctu.edu.tw



Reply With Quote
  #6  
Old   
Nils Valentin
 
Posts: n/a

Default Re: Request modelling - 07-11-2003 , 09:51 PM


Hi Bruce;

Thank you for the reply.

I passed the core exam but obviously its not enough ;-) Live and learn ;-)

Thank you very much for the reply.

Best regards

Nils Valentin


2003=E5=B9=B4 7=E6=9C=88 12=E6=97=A5 =E5=9C=9F=E6=9B=9C=E6=97=A5 10:20=E3=
=80=81Bruce Feist =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=BE=E3=
=81=97=E3=81=9F:
Quote:
Nils Valentin wrote:
I would like to list all courses with have less than lets say 10
participants. Does anybody know how I would have to modify the code bel=
ow
to get this to work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually within
the WHERE (clause which is not allowed).

That's precisely why HAVING exists.

SELECT schedule_id,count(schedule_id)
FROM attendance
HAVING count(schedule_id) <10
GROUP BY schedule_id ;

Bruce Feist
=2D-=20
=2D--
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3...ie.nctu.edu.tw



Reply With Quote
  #7  
Old   
Nils Valentin
 
Posts: n/a

Default Re: Request modelling - 07-11-2003 , 09:55 PM


Hi Bruce,

Thank you for the reply.

Just for the record the options order must be

SELECT schedule_id,count(schedule_id)=20
=46ROM attendance=20
GROUP BY schedule_id=20
HAVING count(schedule_id) <10;=20

(swapped GROUP BY and HAVING)

Best regards

Nils Valentin
Tokyo/Japan


2003=E5=B9=B4 7=E6=9C=88 12=E6=97=A5 =E5=9C=9F=E6=9B=9C=E6=97=A5 10:20=E3=
=80=81Bruce Feist =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=BE=E3=
=81=97=E3=81=9F:
Quote:
Nils Valentin wrote:
I would like to list all courses with have less than lets say 10
participants. Does anybody know how I would have to modify the code bel=
ow
to get this to work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually within
the WHERE (clause which is not allowed).

That's precisely why HAVING exists.

SELECT schedule_id,count(schedule_id)
FROM attendance
HAVING count(schedule_id) <10
GROUP BY schedule_id ;

Bruce Feist
=2D-=20
=2D--
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3...ie.nctu.edu.tw



Reply With Quote
  #8  
Old   
Becoming Digital
 
Posts: n/a

Default Re: Request modelling - 07-13-2003 , 08:12 PM


Actually, "Live and Learn" is correct. "Life and Learn" makes no sense.

Edward Dudlik
Becoming Digital
www.becomingdigital.com


Did I help you? Want to show your thanks?
www.amazon.com/o/registry/EGDXEBBWTYUU



----- Original Message -----
From: "Nils Valentin" <nils (AT) knowd (DOT) co.jp>
To: "Bruce Feist" <bfeist (AT) flock (DOT) org>
Cc: <mysql (AT) lists (DOT) mysql.com>
Sent: Friday, 11 July, 2003 22:16
Subject: Re: Request modelling


2003年 7月 12日 土曜日 10:55、Nils Valentin さんは書きました:
Quote:
Hi Bruce;

Thank you for the reply.

I passed the core exam but obviously its not enough ;-) Live and learn ;-)
Aeeh thats was supposed to be ... life and learn..
Perhaps somebody knows a good spellchecker for logical mistakes ?? ;-)
That would have immediately popped up on my screen ;-)


Best regards

Nils Valentin
Tokyo/Japan


Quote:
Thank you very much for the reply.

Best regards

Nils Valentin

2003年 7月 12日 土曜日 10:20、Bruce Feist さんは書きました:
Nils Valentin wrote:
I would like to list all courses with have less than lets say 10
participants. Does anybody know how I would have to modify the code
below to get this to work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually
within the WHERE (clause which is not allowed).

That's precisely why HAVING exists.

SELECT schedule_id,count(schedule_id)
FROM attendance
HAVING count(schedule_id) <10
GROUP BY schedule_id ;

Bruce Feist
--
---
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=s...ital (DOT) com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw



Reply With Quote
  #9  
Old   
Nils Valentin
 
Posts: n/a

Default Re: Request modelling - 07-14-2003 , 12:21 AM


Hi Edward;

Thanks fot the reply.

I have a double error then ;-)
=46irst mistaken whats actually correct and then even trying to fix it ;-)
Never mind.

Best regards

Nils Valentin
Tokyo/Japan


2003=E5=B9=B4 7=E6=9C=88 14=E6=97=A5 =E6=9C=88=E6=9B=9C=E6=97=A5 10:10=E3=
=80=81Becoming Digital =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=
=BE=E3=81=97=E3=81=9F:
Quote:
Actually, "Live and Learn" is correct. "Life and Learn" makes no sense.

Edward Dudlik
Becoming Digital
www.becomingdigital.com


Did I help you? Want to show your thanks?
www.amazon.com/o/registry/EGDXEBBWTYUU



----- Original Message -----
From: "Nils Valentin" <nils (AT) knowd (DOT) co.jp
To: "Bruce Feist" <bfeist (AT) flock (DOT) org
Cc: <mysql (AT) lists (DOT) mysql.com
Sent: Friday, 11 July, 2003 22:16
Subject: Re: Request modelling

2003=E5=B9=B4 7=E6=9C=88 12=E6=97=A5 =E5=9C=9F=E6=9B=9C=E6=97=A5 10:55=E3=
=80=81Nils Valentin =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=BE=
=E3=81=97=E3=81=9F:
Quote:
Hi Bruce;

Thank you for the reply.

I passed the core exam but obviously its not enough ;-) Live and learn
;-)

Aeeh thats was supposed to be ... life and learn..
Perhaps somebody knows a good spellchecker for logical mistakes ?? ;-)
That would have immediately popped up on my screen ;-)


Best regards

Nils Valentin
Tokyo/Japan

Thank you very much for the reply.

Best regards

Nils Valentin

2003=E5=B9=B4 7=E6=9C=88 12=E6=97=A5 =E5=9C=9F=E6=9B=9C=E6=97=A5 10:20=
=E3=80=81Bruce Feist =E3=81=95=E3=82=93=E3=81=AF=E6=9B=B8=E3=81=8D=E3=8 1=BE=
=E3=81=97=E3=81=9F:
Quote:
Nils Valentin wrote:
I would like to list all courses with have less than lets say 10
participants. Does anybody know how I would have to modify the code
below to get this to work ?

schedule_id is the course itself


SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually
within the WHERE (clause which is not allowed).

That's precisely why HAVING exists.

SELECT schedule_id,count(schedule_id)
FROM attendance
HAVING count(schedule_id) <10
GROUP BY schedule_id ;

Bruce Feist

--
---
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =20
http://lists.mysql.com/mysql?unsub=3...ital (DOT) com
=2D-=20
=2D--
Valentin Nils
Internet Technology

E-Mail: nils (AT) knowd (DOT) co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3...ie.nctu.edu.tw



Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top