Solved

query is retrieving students who have no hours

Posted on 2014-09-26
7
87 Views
Last Modified: 2014-09-27
In my statement below, I get a complete list of all  the Agency's from tblOrgProfile.  That is good. However, in the output, the count(distinct r.RegID) is including those who have 0 hours.  Where do I place this expression in the query to avoid getting a count of those who have 0 hours.  

AND h.Hours > 0


       esql = "DECLARE @fromdt AS datetime " & _
                "DECLARE @todt AS datetime " & _
                "SET @fromdt = '" & DTPicker1 & "' " & _
                "SET @todt = '" & DTPicker2 & "' " & _
                "SELECT  p.Agency, count(distinct r.RegID) " & _
                "FROM tblOrgProfile as p " & _
                "LEFT JOIN tblOrgRegistrations as r " & _
                    "ON p.AgencyID = r.AgencyID " & _
                    "AND r.AgeRegistration <= 7 " & _
                "LEFT JOIN tblOrgHours as h " & _
                    "ON h.RegID = r.regid " & _
                    "AND h.ActivityDate >= @fromdt " & _
                    "AND h.ActivityDate <= @todt " & _
                    "AND h.Hours > 0 " & _
                "GROUP BY p.Agency " & _
                "ORDER BY p.Agency"

Open in new window

0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40347222
I suggest you change line 5 to count from h instead of r

                "SELECT  p.Agency, count(distinct h.RegID) " & _

I believe you have the "AND h.Hours > 0 " in the correct place
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40347236
>FROM tblOrgProfile as p " & _
>LEFT JOIN tblOrgRegistrations as r " & _
LEFT JOIN is essentially saying 'Give me all the rows in the left table (tblOrgProfile), and populate with the values in the right table (tblOrgRegistrations), which guarantees that you'll get counts of zero if there are no related rows in the right table.

If you do not wish these zeros, change LEFT JOIN to just JOIN.
0
 

Author Comment

by:al4629740
ID: 40347979
Portlet,  your change is correct.  What I don't understand is why?  I thought it should be r.RegID.  Why would it be h.RegID?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40348007
The aggregate function COUNT() has a very useful property: It only counts NON-NULL values.

Because you do not want to count if hours are 0 what we need to ensure is that there are NULLs when hours = 0
(and NULL if hours do not exist at all)

The table which contains hours is:  "LEFT JOIN tblOrgHours as h "
so, we will get NULLs if no hours exist (due to the left join)

and we will also get NULLs if hours = 0 due to the join condition: "AND h.Hours > 0 "

so, the correct place to count hours based information, where we get NULLs to suit our need,  is "tblOrgHours as h"
not "tblOrgRegistrations as r"

---
As a general rule choose a field from the table that specifically holds the information you need to count
You were counting registrations
Now you are counting registrations that have non-null & non-zero hours
0
 

Author Comment

by:al4629740
ID: 40348107
Boy I thought that when I was looking for number of registered students that the Registration table would have been the right choice, but now I have to rethink the way I think....
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40348121
Yes, you are correct if you were only counting registrations
BUT you want more conditions (registrations where hours > 0)
so you need to move focus to where the hours are recorded.

Perhaps this will help:
it is due to use of COUNT(DISTINCT  RegID)     that you are still counting number of registrations

it is due to the    h.      that you are only counting when hours > 0
0
 

Author Comment

by:al4629740
ID: 40348184
That helps better.  I have to think in terms of unique registrations that are tied to hours > 0 and put emphasis on the core element which is hours > 0

Hours > 0 is what makes me get the desired results
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question