Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

query is retrieving students who have no hours

Posted on 2014-09-26
7
Medium Priority
?
90 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 49

Accepted Solution

by:
PortletPaul earned 2000 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 66

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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 49

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 49

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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