Hello.
I have just spent the afternoon pouring over the mailing list archives and
the manual trying to find a way to do this, but have come up pretty empty,
so I decided it was time to ask the list.
I have a database that our sales team uses to track their sales efforts.
For the sake of simplicity, and to make this problem easier to digest, it
can be described in two tables:
ads = advertisements sold
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| ad_id | int(11) unsigned | | PRI | NULL | auto_increment |
| ad_clientid | int(8) | | | | |
| ad_pubid | int(8) | | | | |
+---------------+------------------+------+-----+---------+----------------+
clients = clients who have purchased ads
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| client_id | int(11) unsigned | | PRI | NULL | auto_increment |
| client_region | char(3) | | | 0 | |
+---------------+------------------+------+-----+---------+----------------+
The easy part (what already works):
We can generate a report that tells us how many ads were sold in each
publication, broken down by region. We can also make that same query tell
us the total number of advertisers in each publication (a single client can
purchase multiple ads in a single publication), like this:
select
ad_pubid,
count(ad_pubid) as ads_sold,
count(distinct ad_clientid) as advertisers,
SUM(IF(client_region='DL',1,0)) as DL_ads_sold,
SUM(IF(client_region='GN',1,0)) as GN_ads_sold,
SUM(IF(client_region='LK',1,0)) as LK_ads_sold,
SUM(IF(client_region='ME',1,0)) as ME_ads_sold,
SUM(IF(client_region='MN',1,0)) as MN_ads_sold,
SUM(IF(client_region='SC',1,0)) as SC_ads_sold,
SUM(IF(client_region='SW',1,0)) as SW_ads_sold,
SUM(IF(client_region='WM',1,0)) as WM_ads_sold
from ads
inner join clients on ad_clientid = client_id
group by ad_pubid
(the actual query and table structures are much more complex, and include
relationships to make the publication name visible, total prices and other
human-oriented stuff like that, but the above gets the point across)
Problem: Our sales staff wants me to run this same report, but tell them how
many ADVERTISERS bought in each region, NOT the number of ADS SOLD in each
region. So, what I'd like to be able to do is to change the SUM lines to
read something like this (the following doesn't work ;):
SUM(IF(client_region='GN' and distinct(client_id),1,0)) as GN_ads_sold,
What I CAN do is query for each region, like this:
select
ad_pubid,
count(distinct ad_clientid) as WM_Advertisers
from
ads
inner join clients on ad_clientid = client_id
where
client_region = 'WM'
group by
ad_pubid
But, as you can imagine, I don't WANT to do it that way, because it will
mean making EIGHT queries in order to get the numbers I need for a single
report. And I'm trying to get OUT of the
gotta-write-a-fancy-script-to-generate-this-report business, because it just
takes too darned long!
Does anybody know of a way to do this in a single query? It just seems like
it should be SO possible! I did notice that COUNT(distinct expr) can take
multiple expressions, but the documentation is pretty thin in describing
'how it works', so I'm not sure what to expect when giving it more than one
expression, or, for that matter, what to give it for expressions in the
first place! I've tried trial-and-error, and nothing seemed to be yielding
predictable results.
So, I'm off to write a PHP script to generate this silly report. But if
someone comes up with a way to do it without a script, or has any
suggestions about how to do it better, I'm ALL EARS.
Thanks!
-Kevin
--
Kevin Murphy
Director of Technology
Glen Group
Marketing and Advertising Design
P.O. Box 2838, 170 Kearsarge Street
North Conway, New Hampshire 03860
http://www.glengroup.com
Voice: 603-356-3030 | FAX: 603-356-3991
---------------------------------------------------------------------
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
