Solved

query is retrieving students who have no hours

Posted on 2014-09-26
7
88 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing SCCM 2016 4 53
What is GIS method of Geometry data type? 6 36
SQL Server - Multiple Conditions on Left or RIGHT Joins 2 30
Report 8 27
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

752 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