Help - TSQL Question

Question

Post reply

Help - TSQL Question

lassell

Ten Centuries

Points: 1215
More actions
August 15, 2010 at 6:01 pm

#227941

I have the following issue to solve;

I have two tables, one called Application and one called Contacts. The Application table has a unique col called ApplNbr which relates to the Contacts table. I need to develop a sql query that counts the average total number of Contact records for each application.

On reflection, perhaps a better approach might be to total the Contact count for each application and then GROUP BY the Contact Count and count the number of applications for each Contact count i.e.

Contacts Applications

1 10 <---- ie 10 Applications have 1 contact

2 20 <---- ie 20 Applications have 2 contacts etc

How can I achieve this is TSQL? Any help would be appreciated.

Thanks, Phil

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

August 15, 2010 at 6:35 pm

#1207115

Something like this should do the trick.

SELECT COUNT(dtCnts.*) As NbApplications, dtCnts.CntContacts FROM (

SELECT ApplNbr, COUNT(*) As CntContacts FROM dbo.Contacts GROUP BY ApplNbr

) dtCnts

lassell Ten Centuries Points: 1215 More actions · Answer 2

Thanks, but I am still getting issues. Can you break it down further? Sorry, I am a newby.

I am using the following;

SELECT (SELECT distinct COUNT(a.ApplNbr)as "Applications" FROM tApplication a ) as "Applications",(

SELECT distinct COUNT(c.IdentityNbr) as "Contact records" from tApplContact c, tApplication a where c.ApplNbr = a.ApplNbr ) as "Contacts"

but this just returns

Applications Contacts

30202 1254

I really need to acheive

Applications Contacts

10 2 -- 10 applications have 2 contacts

39 4 -- 39 aplications have 4 contacts

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 3

With what you've got there is essentially select count(a.*), count(b*)

First, get the data back. My guess is you want something like:

select a.applno



 from application a

   inner join contacts c

     on a.applno = c.applno

Then you would add in the aggregate, however I"m not sure what averate total is? You have a single count per contract, not an average. There would be an overall average, but how it there an average per contract?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

lassell Ten Centuries Points: 1215 More actions · Answer 4

Hi,

Thanks Steve.

I've not explained this well. 🙂

Using the following returns what I want , however;

SELECT (SELECT distinct COUNT(a.ApplNbr)as "Applications" FROM tApplication a ) as "Applications",(

SELECT distinct COUNT(c.IdentityNbr) as "Contact records" from tApplContact c, tApplication a where c.ApplNbr = a.ApplNbr ) as "Contacts"

This just returns;

Applications ContactNumbers

206 13

I need the data grid to show the following;

Applications ContactNumbers

10 2 (10 of my apps have 2 contacts)

42 7 (42 of mu apps have 7 contacts)

66 1

88 3

lassell Ten Centuries Points: 1215 More actions · Answer 5

Done it 🙂 . However, please feel free to let me know if this syntax is considered bad practise in any way.

IF OBJECT_ID('vwAppCounter') IS NOT NULL

DROP VIEW vwAppCounter

GO

CREATE VIEW vwAppCounter

AS

SELECT ApplNbr, COUNT(*) As Client_Contacts FROM tApplContact GROUP BY ApplNbr

GO

SELECT DISTINCT Client_Contacts , COUNT(ApplNbr)AS "Application_Count" FROM vwAppCounter

GROUP BY Client_Contacts order by Client_Contacts

DROP VIEW vwAppCounter

returns;

Client_Contacts, Application_Count

1, 3324

2, 324

3, 62

4, 15

5, 4

6, 4

7, 1

8, 1

9, 2

11, 1

12, 2

21, 1

Thanks for your help guys.

Phil

jborne007 SSC Enthusiast Points: 105 More actions · Answer 6

jborne007

SSC Enthusiast

Points: 105

August 15, 2010 at 8:11 pm

#1207130

Do you need this in a View?

lassell Ten Centuries Points: 1215 More actions · Answer 7

lassell

Ten Centuries

Points: 1215

August 15, 2010 at 8:24 pm

#1207131

Hi,

I guess this would be better if I didnt use a view, but is it possible?

Cheers, Phil

Mansfield SSCrazy Points: 2366 More actions · Answer 8

Yes, it is. For example:



SELECT DISTINCT

    w.Client_Contacts,

    COUNT(w.ApplNbr) AS "Application_Count"

FROM (

    SELECT ApplNbr,

           COUNT(*) As Client_Contacts

    FROM tApplContact

    GROUP BY ApplNbr

) w

GROUP BY w.Client_Contacts

ORDER BY w.Client_Contacts

You'll notice how the query that constituted the view has been contained in the sub query named "w".

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 9

Honestly, this is a place I'd use a CTE since it's cleaner, IMHO, to read. And it's becoming a bit of a convention in code.

With W (applnbr, application_count)



as

(

    SELECT ApplNbr,

           COUNT(*) As Client_Contacts

    FROM tApplContact

    GROUP BY ApplNbr

)

SELECT DISTINCT

    w.Client_Contacts,

    COUNT(w.ApplNbr) AS "Application_Count"

FROM  w

GROUP BY w.Client_Contacts

ORDER BY w.Client_Contacts

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

lassell Ten Centuries Points: 1215 More actions · Answer 10

lassell

Ten Centuries

Points: 1215

August 16, 2010 at 2:04 pm

#1207553

Thanks, thats much better than using a view. 🙂

gklundt Ten Centuries Points: 1070 More actions · Answer 11

gklundt

Ten Centuries

Points: 1070

August 16, 2010 at 6:03 pm

#1207621



/* Get count of contact by app */

with ContactsByApp as (

	select ApplNbr  , count(*) as ContactCount

	from Contacts

	group by ApplNbr

)

/* get count of applications by contact count */

select

  count(*) as [Applications used by]

, ContactCount as [this many apps]

from ContactsByApp a

group by ContactCount