More SQL - Counting

Mind... Going... Crazy...

Page 1 of 1

7 Replies - 782 Views - Last Post: 06 October 2008 - 02:56 PM Rate Topic: -----

#1 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

More SQL - Counting

Posted 02 October 2008 - 09:18 AM

Code I have so far:
Select Distinct
	  EmpMe.Status as Status,
	  EmpMe.Full_Name as TheName,
	  NULL as Total_Clients,
	  NULL as Active_Ongoing,
	  NULL as Inactive_Ongoing,
	  NULL as Total_Ongoing,
	  NULL as Active_Retros,
	  NULL as Inactive_Retros,
	  NULL as Total_Retros
FROM Engagements as E
	  INNER JOIN Clients as C on C.ID = E.Client_ID
	  INNER JOIN (
			select a.Client_ID as ID, b.Full_Name, B.Status, a.Position, a.eng_id 
				from Rep_Client a
					inner join Employees b on b.ID = a.Emp_ID
		) EmpMe on C.ID = EmpMe.ID and EmpMe.eng_id is null
WHERE EmpMe.Status = 'Partner'
Order By TheName



The Where EmpMe.Status = ‘Partner’ will be replaced with @status and passed in by the webpage.
What this gives me are the people’s names, and a list of Null’s.
I need to fill in each NULL with the following (Example of one below, if I can get help with that I can do the rest I think)
Active_Ongoing
Count of the number of clients that have 1 or more engagements with the follow attributes:
E.Reports_Out is null
E.Classification = ‘Verification (Ongoing)’
E.Inactive is null

Now obviously this should only count the clients that the employee is assigned to, but I THINK I have that taken care of in my second INNER JOIN.

It does not matter if one client has 1, or 10 engagements that match those criteria it should still only return as 1 additional count, as the point is to count the clients that have 1 or more of such engagements.

Thanks in advance,

Is This A Good Question/Topic? 0
  • +

Replies To: More SQL - Counting

#2 ajwsurfer  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 373
  • Joined: 24-October 06

Re: More SQL - Counting

Posted 02 October 2008 - 11:49 AM

If I am not mistaken, this is a question that belongs in the "Databases" form.
Was This Post Helpful? 0
  • +
  • -

#3 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: More SQL - Counting

Posted 02 October 2008 - 01:10 PM

Not sure, I'm accessing it using ASP.net and VB.net. One of the moderators moved my last similar question to this forum.
Was This Post Helpful? 0
  • +
  • -

#4 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: More SQL - Counting

Posted 02 October 2008 - 01:53 PM

Moved to Database.

Regardless of the language you are programming in, this and the other question are both related to SQL syntax. Therefore they belong in the Database forum.

I moved the other topic into this forum as well.
Was This Post Helpful? 0
  • +
  • -

#5 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: More SQL - Counting

Posted 02 October 2008 - 02:08 PM

Thanks, I'll use this forum from now on :0).
Anyway I'm close!
However my count function is not returning what I want.
What it’s doing is for any client that has an engagement that matches my criteria, it’s counting ALL of the engagements.
For instance if I have 2 clients, 1 of them has 4 engagements, 1 of which matches the criteria, the count returns 4. (Should return 1)
If I set the second client to have 5 engagements, 1 of which matches the criteria, the count returns 9. (Should return 2)
I thought my Distinct in the left join would prevent that....

Select Distinct
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	NULL as Total_Clients,
	count(Active_O.Client_ID) as Active_Ongoing,
	NULL as Inactive_Ongoing,
	NULL as Total_Ongoing,
	NULL as Active_Retros,
	NULL as Inactive_Retros,
	NULL as Total_Retros
FROM Engagements as E
	INNER JOIN Clients as C on C.ID = E.Client_ID
	INNER JOIN (
			select a.Client_ID as ID, b.Full_Name, B.Status, a.Position, a.eng_id 
				from Rep_Client a
					inner join Employees b on b.ID = a.Emp_ID
		) EmpMe on C.ID = EmpMe.ID and EmpMe.eng_id is null
	LEFT OUTER JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
		) Active_O on C.ID = Active_O.Client_ID
WHERE EmpMe.Status = 'Partner'
Group By EmpMe.Status, EmpMe.Full_Name
Order By TheName

Was This Post Helpful? 0
  • +
  • -

#6 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: More SQL - Counting

Posted 03 October 2008 - 06:04 AM

You should try to work without distinct, it might not do what you expect.
Actually, if you think you need a distinct, you probably have a problem in a join and are getting duplicates because of it.

Why is the second inner join with a subquery? A normal duo of joins on Rep_Client and Employees would work fine (better)
If you need particular parts of a join to be null, do not use a inner join but outer join and a "where x.id is null" in the whereclause.

The same can be done with the second subquery-join.

I would suggest you first cut out the group and count, and test the results by viewing the actual output list before counting and grouping etc. That way it is much more easy to spot why things behave different then you expect.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#7 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: More SQL - Counting

Posted 06 October 2008 - 02:08 PM

I've changed the INNER JOIN that you mentioned to having two seperate INNER JOIN's instead of the subquery.
So far same result (that is good though, at least it didn't mess anything up).
Thank you.

I do not know of a way to NOT use distinct.
This is what I am trying to accomplish.
For any client that has one or more engagements that meet a criteria, count one for them.
So let's say I have 100 engagements and 40 of them meet the criteria. I don't want it to return a count of 40, I want it to return the number of Clients. Thus if I do distinct client_id, it should return (for instance) 5.

When i run the subquery:
select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL

That works great and returns the number I expect.

That query within my main one (now looks like this):
Select
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	NULL as Total_Clients,
	count(Active_O.Client_ID) as Active_Ongoing,
	count(InActive_O.Client_ID) as Inactive_Ongoing,
	NULL as Total_Ongoing,
	NULL as Active_Retros,
	NULL as Inactive_Retros,
	NULL as Total_Retros
FROM Engagements as E
	INNER JOIN Clients as C on C.ID = E.Client_ID
	INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
	INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
	LEFT JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
		) As Active_O on C.ID = Active_O.Client_ID and RC.Client_ID = Active_O.Client_ID and E.Client_ID = Active_O.Client_ID
	LEFT JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NOT NULL
		) As InActive_O on C.ID = InActive_O.Client_ID
WHERE EmpMe.Status = 'Partner' and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By TheName

That returns the number of engagements. I've seen this happen before and so far it's always been with a goof in the join (missing a "something.id = somethingelse.id") But as you may notice I've thrown in a bunch of extra of those, and I'm still getting the same improper result...
Was This Post Helpful? 0
  • +
  • -

#8 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: More SQL - Counting

Posted 06 October 2008 - 02:56 PM

Ok I (think) I got it using Distinct within the Count function!
Select
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	NULL as Total_Clients,
	count(distinct Active_O.Client_ID) as Active_Ongoing,
	count(distinct InActive_O.Client_ID) as Inactive_Ongoing,
	NULL as Total_Ongoing,
	NULL as Active_Retros,
	NULL as Inactive_Retros,
	NULL as Total_Retros
FROM Engagements as E
	INNER JOIN Clients as C on C.ID = E.Client_ID
	INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
	INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
		) As Active_O on C.ID = Active_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NOT NULL
		) As InActive_O on C.ID = InActive_O.Client_ID
WHERE EmpMe.Status = 'Partner' and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By TheName

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1