Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

counting

0 views
Skip to first unread message

Fabio Bernardo

unread,
May 5, 2003, 3:47:24 PM5/5/03
to
------_=_NextPart_000_01C3133E.5F51C3C0
Content-Type: text/plain;
charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable

About this mesage...=20

Posted by glenn on Tuesday July 30 2002, @9:17pm [Delete] [Edit]=20

It would be GREAT to see an example of count using=20
2 tables. For example one table contains artists and=20
another table contains a list of works and use count=20
to get a list of all artists and how many works they=20
have done (including artists who have no works ie.=20
count =3D 0 )=20

Did you get any answer? I=B4m having the same problem and I=B4d like to =
see some
examples....=20
thanks=20


------_=_NextPart_000_01C3133E.5F51C3C0
Content-Type: message/rfc822

Message-ID: <6019F2AD13E6D611AFB6...@mshqt02.hqt.intelig>
From: Fabio Bernardo <fabio.b...@intelig.net.br>
To: "'my...@mindfloat.com'" <my...@mindfloat.com>
Subject: hi there
Date: Mon, 5 May 2003 16:38:51 -0300
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2654.89)
Content-Type: multipart/mixed;
boundary="----_=_NextPart_002_01C3133E.5F51C3C0"


------_=_NextPart_002_01C3133E.5F51C3C0
Content-Type: text/plain;
charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable

About this mesage...=20

Posted by glenn on Tuesday July 30 2002, @9:17pm [Delete] [Edit]=20

It would be GREAT to see an example of count using=20
2 tables. For example one table contains artists and=20
another table contains a list of works and use count=20
to get a list of all artists and how many works they=20
have done (including artists who have no works ie.=20
count =3D 0 )=20

Did you get any answer? I=B4m having the same problem and I=B4d like to =
see some
examples....=20
thanks=20


------_=_NextPart_002_01C3133E.5F51C3C0--


------_=_NextPart_000_01C3133E.5F51C3C0
Content-Type: text/plain; charset=us-ascii

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw
------_=_NextPart_000_01C3133E.5F51C3C0--

Bruce Feist

unread,
May 5, 2003, 5:10:19 PM5/5/03
to
SELECT a.artist_id, a.artist_name, count(*)
FROM artist a LEFT JOIN w work ON a.artist_id = w.artist_id
GROUP BY a.artist_id, a.artist_name

We LEFT JOIN because we want all artists included, whether or not they
have works. We GROUP BY both artist_id and artist_name because in some
dialects of SQL it is illegal to SELECT anything that is not an
aggregate and is not in the GROUP BY. (MySQL permits, this, but why not
stay portable?) We select both artist_id and artist_name because it is
conceivable that two artists might have the same name.

Bruce Feist

Fabio Bernardo wrote:

>Posted by glenn on Tuesday July 30 2002, @9:17pm [Delete] [Edit]
>

>It would be GREAT to see an example of count using

>2 tables. For example one table contains artists and

>another table contains a list of works and use count

>to get a list of all artists and how many works they

>have done (including artists who have no works ie.

>count = 0 )
>
>Did you get any answer? I´m having the same problem and I´d like to see some
>examples....

Gordon

unread,
May 7, 2003, 3:37:07 AM5/7/03
to
Something like this

select cpny_Name, acct_Name, list_Name, count(addr_ID) as list_Qty=20
from companys
inner join accounts
using (cpny_ID)
inner join lists
using (cpny_ID, acct_ID)
inner join addresses
using (cpny_ID, acct_ID, list_ID)
where companys.cpny_ID =3D 'RER1' and=20
accounts.acct_ID =3D 'CA03'=20
group by lists.cpny_ID, lists.acct_ID, lists.list_ID

mysql> select cpny_Name, acct_Name, list_Name, count(addr_ID) as
list_Qty
-> from companys
-> inner join accounts
-> using (cpny_ID)
-> inner join lists
-> using (cpny_ID, acct_ID)
-> inner join addresses
-> using (cpny_ID, acct_ID, list_ID)
-> where companys.cpny_ID =3D 'RER1' and
-> accounts.acct_ID =3D 'CA03'
-> group by lists.cpny_ID, lists.acct_ID, lists.list_ID;
+--------------+--------------+--------------------------------+--------
--+
| cpny_Name | acct_Name | list_Name |
list_Qty |
+--------------+--------------+--------------------------------+--------
--+
| Jones Realty | John Jones I | THT - CHEROKEE-RIDGEWOOD HILLS |
283 |
| Jones Realty | John Jones I | THT - LAKE RIDGE |
116 |
| Jones Realty | John Jones I | MOCA |
96 |
| Jones Realty | John Jones I | THT - RASPBERRY HILLS |
39 |
| Jones Realty | John Jones I | THT - RASPBERRY MEADOWS |
19 |
| Jones Realty | John Jones I | THT - ROLLING HILLS |
57 |
| Jones Realty | John Jones I | THT - PAT JORDANS ADDITION |
41 |
| Jones Realty | John Jones I | Midway List |
509 |
+--------------+--------------+--------------------------------+--------
--+
8 rows in set (1.67 sec)

> -----Original Message-----
> From: Fabio Bernardo [mailto:fabio.b...@intelig.net.br]=20
> Sent: Monday, May 05, 2003 2:42 PM
> To: my...@lists.mysql.com
> Subject: counting
>=20
>=20
>=20
>=20
> About this mesage...=20
>=20
> Posted by glenn on Tuesday July 30 2002, @9:17pm [Delete] [Edit]=20


>=20
> It would be GREAT to see an example of count using=20
> 2 tables. For example one table contains artists and=20
> another table contains a list of works and use count=20
> to get a list of all artists and how many works they=20
> have done (including artists who have no works ie.=20
> count =3D 0 )=20

>=20
> Did you get any answer? I=B4m having the same problem and I=B4d=20
> like to see some examples....=20
> thanks=20
>=20
>=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmy...@freebsd.csie.nctu.edu.tw

0 new messages