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
Solved

query is retrieving students who have no hours

Posted on 2014-09-26
7
86 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Suggested Solutions

Title # Comments Views Activity
how to just get time from a date 6 32
Set WorkSheet  not Working 9 40
Current Month Filter in Visual Studio 10 22
MS SQL Delete Duplicate Rows Only 2 16
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 the fundamental information of how to create a table.

860 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