Solved

query is retrieving students who have no hours

Posted on 2014-09-26
7
83 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
  • 3
  • 3
7 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now