I have two select statements. The only different between them is in the where
clause. Instead of returning "Community, Monthly Total" and "Community, YTD",
I'd really like one statement to return "Community, Monthly Total, YTD".

Any suggestions?

Thanks,
Randy

select cr.ecity as 'Community',
    count(c.complainerid) as 'Monthly Total'
    from complainer as cr
    left join complaint as c on cr.complainerid = c.complainerid
    where c.incidentdt between '05/01/2006' and '06/01/2006' and cr.ecity is
not null
    group by (cr.ecity)
select cr.ecity as 'Community',
    count(c.complainerid) as 'YTD Total'
    from complainer as cr
    left join complaint as c on cr.complainerid = c.complainerid
    where c.incidentdt between '01/01/2006' and '06/01/2006' and cr.ecity is
not null
    group by (cr.ecity)


Thanks,

Try this :

select cr.ecity as 'Community',
count(c1.complainerid) as 'Monthly Total',
count(c2.complainerid) as 'YTD Total'
from complainer as cr
left join complaint as c1 on cr.complainerid = c1.complainerid
left join complaint as c2 on cr.complainerid = c2.complainerid
    where c1.incidentdt between '05/01/2006' and '06/01/2006'
    and c2.incidentdt between '01/01/2006' and '06/01/2006'
    and cr.ecity is not null
    group by (cr.ecity)

Regards,
Sylvain

Show quoteHide quote

Many thanks for the response. The problem below is that I only get a subset
of towns that are common to both groups. I'm still struggling to get a count
for each town, even if the sount is zero.
Show quoteHide quote

Many, many thanks for all the answers. I did rush the typing a bit when I
asked the original question. Tom actually saw through that and went way above
and beyond the call. Those insights were exactly what I was looking for.

Thanks again!
Show quoteHide quote

This will generate incorrect results.  Suppose a community has 3 complaints,
all 3 in the current year, and 2 of them in the current month..  Then the
first left join will match that community with all three rows in complaint
giving 3 rows.  Then the second left join will match these three rows up
with all three rows in the commplaint table again, giving 9 rows total, one
for each possible combination of dates.  Then the where clause will discard
the three rows of those nine that have the incidentdt from c1 which is not
in the month you are looking at.  This leaves you with 6 rows, all of which
have valid values in c1.complainerid and c2.complainerid, so both Monthly
Total and YTD Total will show as 6.

Tom

Show quoteHide quote

Here is an example so you can see what occurs (I used this since you didn't send any sample data.)

SELECT
     c.CustomerID
   , 'Quarter Sales' = count( o.OrderID )
   , 'Annual Sales'  = ( SELECT count(1)
                         FROM Orders
                         WHERE (   CustomerID = c.CustomerID
                               AND OrderDate BETWEEN '1/1/1997' AND '12/31/1997'
                               )
                        )
FROM Customers c
   LEFT JOIN Orders o
      ON c.CustomerID = o.CustomerID
WHERE OrderDate BETWEEN '1/1/1997' AND '3/31/1997'
GROUP BY c.CustomerID

Here is how you may revise your two queries into one. --But I couldn't test it.

SELECT
     'Community'     = cr.eCity
   , 'Monthly Total' = count(c.ComplainerID)
   , 'YTD Total'     = ( SELECT count( ComplainerID )
                         FROM Complaint
                         WHERE (   ComplainerID = c.ComplainerID
                               AND IncidentDt BETWEEN '01/01/2006' AND '06/01/2006'
                               )
                       )
FROM Complainer cr
   LEFT JOIN Complaint c
      ON cr.ComplainerID = c.ComplainerID
WHERE (   c.IncidentDt BETWEEN '05/01/2006' AND '06/01/2006'
      AND cr.eCity IS NOT NULL
      )
GROUP BY cr.eCity

Show quoteHide quote

A couple of questions about your question.

First, you left join won't work correctly in all cases.  If you want to show
a 0 for the count for an ecity that has no complaint rows (not just no
matching rows in the date range you are interested in, but no matching rows
at all), then your queries won't do that correctly.  The reason is that
first you do a left join, and when you have a complainer row that has no
matching complaint rows, a result is built with the data from complainer and
all NULLs in the columns from complaint.  Then your WHERE condition tests
c.incidentdt for the correct BETWEEN values.  Since c.incidentdt is NULL for
this ecity value, the WHERE condition is not true, so the row is discarded.
The fix is to move the c.incidentdt between ... test from the WHERE clause
to the ON clause of the join.

Second, is it true that the Monthly date range is always a subset of the YTD
daterange?  If so, then it is easy to write the select statement so that
there is only one pass thru the complaint table.

Finally, (pet peeve of mine), it is best to always include an ORDER BY
clause in every SELECT statement unless you do not care what order the
results come back in.  Since I suspect you do want the result to be ordered
by cr.ecity, you should include an order by clause specifying that.

So, to answer your question, assuming you want a left join so that ecity's
that have no complaints show up with a count of zero (if that's not true,
just replace the left join with an inner join), and assuming that the
Monthly date range is always a subset of the YTD range, then your query
could look like:

select cr.ecity as 'Community',
sum(case when c.incidentdt between '05/01/2006' and '06/01/2006' then 1 else
0 end) as 'Monthly Total',
count(c.complainerid) as 'YTD Total'
from complainer as cr
left join complaint as c on cr.complainerid = c.complainerid
    and c.incidentdt between '01/01/2006' and '06/01/2006'
where cr.ecity is not null
group by cr.ecity
order by cr.ecity

Tom

Show quoteHide quote

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

The usual way to handle this is to use a report range table, which you
can Google easily.

column dataType
Applying a function to a subquery
Query from a dynamic table
Case statement question
Tuning