this doesnt do the trick as it will produce a table containing the following
columns:
1- stations.name
2- invitations.invitation_status
3- count(*) , which is the count of the number of invitations which have
this status for this particular stations.
this is not what i need, the output should be something like that
stations.name | # of invitations | # confirmed | # cancelled | # postponed
---------------------------------------------------------------------------
-
station A | 30 | 20 | 5
| 5
----------------------------------------------------------------------------
station B | 25 | 14 | 3
| 8
---------------------------------------------------------------------------
-
station C | 47 | 29 | 6
| 12
---------------------------------------------------------------------------
-
to remind you about the problem:
1) stations
-id
-name
2) invitations
-id
-station_id
-invitation_status (confirmed, cancelled, postponed)
I need a query that generate a report about each station with ther
corresponding number of invitations and a breaddown of inviations to show
total number of each invitation_status
----- Original Message -----
From: "Sammy Lau" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, November 30, 2002 6:01 AM
Subject: Re: Complex Query
> try this:
> select a.name, b.invitation_status, count(*) from stations a, invitations
b
> where a.id = b.station_id group by a.name, b.invitation_status
> ----- Original Message -----
> From: "Ahmed Farouk" <[EMAIL PROTECTED]>
> Date: Fri, 29 Nov 2002 22:50:34 +0200
> To: <[EMAIL PROTECTED]>
> Subject: Complex Query
>
>
> > Hi,
> > I need help with a complex query that I can't figure it out myself.
> >
> > Suppose that I have the following tables
> >
> > 1) stations
> > -id
> > -name
> > 2) invitations
> > -id
> > -station_id
> > -invitation_status (confirmed, cancelled, postponed)
> >
> > I need a query that generate a report about each station with ther
> > corresponding number of invitations and a breaddown of inviations to
show
> > total number of each invitation_status
> >
> > stations.name | # of invitations | # confirmed | # cancelled | #
postponed
>
> --------------------------------------------------------------------------
--
> > -----------------------------
> > station A | 30 | 20 | 5
> > | 5
>
> --------------------------------------------------------------------------
--
> > -----------------------------
> > station B | 25 | 14 | 3
> > | 8
>
> --------------------------------------------------------------------------
--
> > -----------------------------
> > station C | 47 | 29 | 6
> > | 12
>
> --------------------------------------------------------------------------
--
> > -----------------------------
> >
> > what query should I use to get the previous output, and if I can't get
these
> > results with a direct query how would I manage to generate this report.
> >
> > Thanks
> > Ahmed
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
