Sometimes, simpler is better. on 4/16/02 4:16 PM, Gurhan Ozen at [EMAIL PROTECTED] wrote:
select ad_pubid, client_region, count(distinct ad_clientid) as Advertisers from ads, clients where ads.ad_clientid = clients.client_id group by ad_pubid, client_region; I think I was too busy trying to make it come out pretty. My public thanks to Gurhan for the simple solution. -Kevin on 4/16/02 3:46 PM, Kevin Murphy at [EMAIL PROTECTED] wrote: > 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
