Grygonos has asked for the wisdom of the Perl Monks concerning the following question:

I have the following query being passed to Access. I want to see tallies of x in relation to their owners y, even if their tally is 0. Currently it only shows x & y where the tallies created by Count() are greater than 0.. Is Access limiting me, or is it just SQL in general.

Forgive this for being off topic but I know alot of monks do DBI work (ie SQL). 
SELECT Count(Referrals.x) AS numberOfx, Referrals.y
FROM Referrals
WHERE (((Referrals.y) Is Not Null) AND ((Referrals.[Today's Date])>#7/
+29/2003#))
GROUP BY Referrals.y
ORDER BY Count(Referrals.x);

[download]
Is there any way I can modify this code to properly grab the tallies even when they tally is 0?

update (broquaint): title change (was Displaying coutned columns when the count is 0)

Replies are listed 'Best First'.
Re: (OT) Displaying coutned columns when the count is 0
by Anonymous Monk on Jul 30, 2003 at 21:19 UTC
    Its SQL in general that is limiting you. Your where clause filters out any ‘Y’ that will be zero, so they simply don’t exist.

    What you want is something like this:

     SELECT
    (SELECT
     Count(t1.x)
     FROM Referrals t1
     WHERE t1.[Today's Date]>#2003/07/29# AND t1.y=t2.y) AS numberOfx,
    t2.y
FROM Referrals AS t2
GROUP BY t2.y

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom