Thank you!
Just what I needed. 8-D I hope these template queries can help you to see
the patterns that evolve while using the GROUP BY with JOINed tables. You
can exclude any unwanted results from the GROUP BY phase of the query by
applying a set of HAVING restrictions. The HAVING clause works *exactly*
like a WHERE clause except it is applied to the *results* of the GROUP BY
and not to the temporary results being aggregated.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
How many bookings happened at a particular location that are part of
project 'x':
SELECT u.User_Location, count(b.bookingID)
FROM Projects p
INNER JOIN Users u
ON u.Client_ID = p.ClientID
AND p.Project_Name = 'x'
INNER JOIN Bookings b
ON b.User_ID = u.User_ID
GROUP BY u.User_Location
How many locations have been booked more than 3 times for the same project
:
SELECT p.Project_Name, u.User_Location, count(b.Booking_ID)
FROM Projects p
INNER JOIN Users u
ON u.Client_ID = p.ClientID
INNER JOIN Bookings b
ON b.User_ID = u.User_ID
GROUP BY p.Project_Name, u.User_Location
HAVING count(b.Booking_ID) >3
How many locations have been booked more than 3 times regardless of
project:
SELECT u.User_Location, count(b.Booking_ID)
FROM Users u
INNER JOIN Bookings b
ON b.User_ID = u.User_ID
GROUP BY u.User_Location
HAVING count(b.Booking_ID) >3
List all locations for all clients participating in project X and how often
they have been booked:
SELECT c.Client_Name, u.User_Location, count(b.bookingID)
FROM Projects p
INNER JOIN Users u
ON u.Client_ID = p.ClientID
AND p.Project_Name = 'X'
INNER JOIN Clients c
on c.Client_ID = p.Client_ID
AND p.Project_Name='X'
LEFT JOIN Bookings b
ON b.User_ID = u.User_ID
GROUP BY c.Client_Name, u.User_Location
- or -
SELECT c.Client_Name, u.User_Location, count(b.bookingID)
FROM Projects p
INNER JOIN Users u
ON u.Client_ID = p.ClientID
INNER JOIN Clients c
on c.Client_ID = p.Client_ID
LEFT JOIN Bookings b
ON b.User_ID = u.User_ID
WHERE p.Project_Name = 'X'
GROUP BY c.Client_Name, u.User_Location
(Both will work but one should be faster. I would try them both to see
which one works faster for you.)
"shaun thornburgh"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL
PROTECTED]
otmail.com> cc:
Fax to:
06/22/2004 04:18 Subject: Re: A Complicated Group
Query
PM
Hi Shawn,
A slight correction(!) Client_ID is contained in the Projects table as a
Client can have many projects. Therefore c.Project_ID will cause an
error...
Here is a definition of the tables:
mysql> DESCRIBE Users;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+----------------------+--------------+------+-----+---------+----------------+
| User_ID | int(11) | | PRI | NULL |
auto_increment |
| Client_ID | int(3) | YES | | NULL |
|
| User_Username | varchar(40) | | | |
|
| User_Password | varchar(20) | YES | | NULL |
|
| User_Name | varchar(100) | | | |
|
| User_Type | varchar(20) | | | Nurse |
|
| User_Email | varchar(100) | YES | | NULL |
|
| User_Location | varchar(40) | YES | | NULL |
|
+----------------------+--------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)
mysql> DESCRIBE Projects;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| Project_ID | int(11) | | PRI | NULL | auto_increment |
| Project_Name | varchar(100) | | | | |
| Client_ID | int(11) | | | 0 | |
+--------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> DESCRIBE Clients;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| Client_ID | int(11) | | PRI | NULL | auto_increment |
| Client_Name | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> DESCRIBE Bookings;
+-------------------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default
|
Extra |
+-------------------------+-------------+------+-----+---------------------+----------------+
| Booking_ID | int(11) | | PRI | NULL
|
auto_increment |
| Booking_Type | varchar(15) | | | Unavailability
|
|
| User_ID | int(11) | | | 0
|
|
| Project_ID | int(11) | YES | | NULL
|
|
| Booking_Creator_ID | int(11) | YES | | NULL
|
|
| Booking_Creation_Date | datetime | YES | | NULL
|
|
| Booking_Start_Date | datetime | | | 0000-00-00 00:00:00
|
|
| Booking_End_Date | datetime | | | 0000-00-00 00:00:00
|
|
| Booking_Completion_Date | date | YES | | NULL
|
|
| Booking_Mileage | int(5) | YES | | NULL
|
|
| Booking_Status | varchar(15) | | | Other
|
|
| Additional_Notes | text | YES | | NULL
|
|
+-------------------------+-------------+------+-----+---------------------+----------------+
20 rows in set (0.01 sec)
mysql>
Thanks a million for your help here :)
>From: [EMAIL PROTECTED]
>To: "shaun thornburgh" <[EMAIL PROTECTED]>
>CC: [EMAIL PROTECTED]
>Subject: Re: A Complicated Group Query
>Date: Tue, 22 Jun 2004 14:32:14 -0400
>
>
>OOPS! That's because my second query should have been a LEFT JOIN and not
a
>RIGHT JOIN (I hate making silly cut-and-paste errors like that) 8-{
>
>If I understand you correctly, you want to see a count of how many
bookings
>you have had per project broken down by user location. Try this:
>
>SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
>FROM Clients c
>LEFT JOIN Users u
> on u.Client_ID = c.Client_ID
>LEFT JOIN Bookings b
> on b.User_ID = u.User_ID
>GROUP BY c.Project_ID, u.User_Location
>
>Or for just one project, we can do this:
>
>SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
>FROM Clients c
>LEFT JOIN Users u
> on u.Client_ID = c.Client_ID
> and c.Project_ID = 'x'
>LEFT JOIN Bookings b
> on b.User_ID = u.User_ID
>GROUP BY c.Project_ID, u.User_Location
>
>--- or this ----
>
>SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
>FROM Clients c
>LEFT JOIN Users u
> on u.Client_ID = c.Client_ID
>LEFT JOIN Bookings b
> on b.User_ID = u.User_ID
>WHERE c.Project_ID = 'x'
>GROUP BY c.Project_ID, u.User_Location
>
>--- or even this ---
>
>SELECT u.User_Location, COUNT(b.booking_ID) as Bookings
>FROM Clients c
>INNER JOIN Users u
> on u.Client_ID = c.Client_ID
> and c.Project_ID = 'x'
>LEFT JOIN Bookings b
> on b.User_ID = u.User_ID
>GROUP BY u.User_Location
>
>If you have any NULL "user_location"s that's because you have at least one
>Client that is part of a project but that Client has no Users. In my last
>sample I changed the first LEFT JOIN to an INNER JOIN so that I would only
>retrieve those Clients (that are part of project 'x') that actually have
>Users. I could have also eliminated them on the way out of the query (and
>after the group by) like this
>
>SELECT u.User_Location, COUNT(b.booking_ID) as Bookings
>FROM Clients c
>LEFT JOIN Users u
> on u.Client_ID = c.Client_ID
> and c.Project_ID = 'x'
>LEFT JOIN Bookings b
> on b.User_ID = u.User_ID
>GROUP BY u.User_Location
>HAVING u.User_ID IS NOT NULL
>
>As you can see, there are many ways to build the report you want and you
>should use the one that performs best for you.
>
>There _is_ a difference in putting c.Project_ID = 'x' in the ON clause
>and in the WHERE clause. In the ON clause those rows are eliminated BEFORE
>the temporary internal table is built. In the WHERE clause you are
>filtering on all of the rows. I am going to make up some round numbers to
>illustrate. Say you have 50 Clients where 5 of them are part of project
>'x'. You have 100 Users and 500 Bookings.
>Every Client has at least 1 user and every User has at least 1 Booking
>(just for argument's sake). This query:
>
>SELECT *
>FROM Clients, Users, Bookings
>
>(or , alternatively)
>
>SELECT *
>FROM Clients
>INNER JOIN Users
>INNER JOIN Bookings
>
>will contain all of the columns of each the three tables and will return
>(50x100x500) or 2500000 rows. This is a "cartesian product" of the three
>tables. The ON clauses of the JOIN statements keep that to a reasonable
>number. Lets say that this is a perfectly balanced world and each User has
>had 5 bookings and that each Client has 2 users then this query
>
>SELECT *
>FROM Clients c
>INNER JOIN Users u
> ON u.Client_ID = c.Client_ID
>INNER JOIN Bookings b
> ON b.User_ID = u.UserID
>
>will result in only (50x2x5) or 500 rows. It's much easier for the WHERE
>clause to scan the resulting columns (assuming no index exists) in a
result
>set this size than one containing 2.5 million rows.
>
>Adding another restriction to one of the ON clauses helps us out even more
>
>SELECT *
>FROM Clients c
>INNER JOIN Users u
> ON u.Client_ID = c.Client_ID
> AND c.Project_ID='x'
>INNER JOIN Bookings b
> ON b.User_ID = u.UserID
>
>Results in only (5x2x5) or 50 rows. If you had any additional restrictions
>(like in a WHERE clause) you only have to apply those to a dataset of 50
>rows. What if we had performed a GROUP BY? In the first case we would
have
>had to GROUP 2.5 million rows, in the second 500 rows and in the last only
>50.
>
>You cannot always put all of your restrictions into the ON clause of a
>JOIN. Let's say you wanted to know what Clients didn't have any users
>assigned to them. You need this query:
>
>SELECT c.Client_Name
>FROM Clients c
>LEFT JOIN Users u
> on u.Client_ID = c.Client_ID
>WHERE u.Client_ID IS NULL
>
>In this case you would need the _results_ of the JOIN in order to see
which
>rows didn't match up. You can't put the IS NOT NULL into the ON clause as
>none of your Client's User_IDs are actually null. It's the internal
results
>of the LEFT JOIN that will have NULL values for all of your Users columns
>for those rows where the IDs didn't match.
>
>Yours,
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>
>
> "shaun thornburgh"
> <[EMAIL PROTECTED] To:
>[EMAIL PROTECTED], [EMAIL PROTECTED]
> otmail.com> cc:
> Fax to:
> 06/22/2004 12:08 Subject: Re: A
>Complicated Group Query
> PM
>
>
>
>
>
>
>Hi Shawn,
>
>Thanks for your reply, I am still having problems here though!
>
>The first query produces this:
>
>+---------------------+---------------+
>| COUNT(B.Booking_ID) | User_Location |
>+---------------------+---------------+
>| 1818 | NULL |
>| 1 | 204 |
>| 1 | 301 |
>| 1 | 302 |
>| 1 | 408 |
>+---------------------+---------------+
>
>
>The location counts here are correct except for the NULL, but the other
>locations havent been included.
>
>The second query, produces a complete list of all the Users locations!
>
>There is another table that might help here; Clients. Each User is
>referenced to a client and each project is referenced to a client. So a
>client has many projects and many users in the database. If I
>modify my initial query to:
>
>SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN
>Bookings B ON U.User_ID = B.User_ID WHERE U.Client_ID = 'X' GROUP
>BY(U.User_Location);
>
>It works perefectly for all clients that have one project. But I need to
be
>
>able to produce the same report for individual projects rather than
>clients!
>
>I hope you can help me here, many thanks!
>
> >From: [EMAIL PROTECTED]
> >To: "shaun thornburgh" <[EMAIL PROTECTED]>
> >CC: [EMAIL PROTECTED]
> >Subject: Re: A Complicated Group Query
> >Date: Tue, 22 Jun 2004 10:12:22 -0400
> >
> >
> >Hi Shaun,
> >
> >You said:
> > >SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER
>JOIN
> > >Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP
> > >BY(U.User_Location);
> > >
> > >It returns all User Locations in the User table, regardless of the
> > >Project_ID of the Booking.
> >
> >That's exactly how the LEFT JOIN is supposed to work. The users are on
>the
> >LEFT side of the LEFT JOIN so you will get _all_ rows from that table
and
> >only those rows from the table on the *other* side of the join that
>satisfy
> >your ON conditions.
> >
> >You can flip your query two ways. Change either LEFT JOIN to RIGHT JOIN
>or
> >exchange the positions of the table names around the JOIN keywords
> >
> >SELECT COUNT(B.Booking_ID), U.User_Location
> >FROM Users U
> >RIGHT OUTER JOIN Bookings B
> > ON U.User_ID = B.User_ID
> > AND B.Project_ID = '10'
> >GROUP BY(U.User_Location);
> >-OR-
> >SELECT COUNT(B.Booking_ID), U.User_Location
> >FROM Bookings B
> >RIGHT OUTER JOIN Users U
> > ON U.User_ID = B.User_ID
> > AND B.Project_ID = '10'
> >GROUP BY(U.User_Location);
> >
> >Either one of these statements puts the Bookings table into the
> >"controlling" side of the partial join.
> >
> >
> >Yours,
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >
> >
> > "shaun thornburgh"
> > <[EMAIL PROTECTED] To:
> >[EMAIL PROTECTED]
> > otmail.com> cc:
> > Fax to:
> > 06/22/2004 09:41 Subject: A Complicated
> >Group Query
> > AM
> >
> >
> >
> >
> >
> >
> >Hi,
> >
> >I have three tables (among others!) in my database; Bookings, Users and
> >Projects. Users have location codes and can make bookings which are for
a
> >particular project.
> >
> >Using the following query I can get a list of all Locations (Each user
>has
> >a
> >Location code) and the number of Bookings that have taken place for that
> >location.
> >
> >SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN
> >Bookings B ON U.User_ID = B.User_ID GROUP BY(U.User_Location);
> >
> >This is useful as it shows zero values where no bookings have taken
>place.
> >However, Each booking is related to a particular project by Project_ID.
>How
> >
> >can I modify this query such that it only returns a count of bookings
>where
> >
> >the Project_ID = 'x'? If I modify this query to:
> >
> >SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN
> >Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP
> >BY(U.User_Location);
> >
> >It returns all User Locations in the User table, regardless of the
> >Project_ID of the Booking. And if I modify the query to:
> >
> >SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN
> >Bookings B ON U.User_ID = B.User_ID WHERE B.Project_ID = '10' GROUP
> >BY(U.User_Location);
> >
> >I lose the zero/NULL Values...
> >
> >Any comments here would be greatly apprecited!
> >
> >_________________________________________________________________
> >It's fast, it's easy and it's free. Get MSN Messenger today!
> >http://www.msn.co.uk/messenger
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
> >
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
> >http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>_________________________________________________________________
>It's fast, it's easy and it's free. Get MSN Messenger today!
>http://www.msn.co.uk/messenger
>
>
>
>
>
>
_________________________________________________________________
Express yourself with cool new emoticons
http://www.msn.co.uk/specials/myemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
