Using two COUNT clauses - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using two COUNT clauses

Author

Topic

konzy_malone
Starting Member

2 Posts

Posted - 2007-11-14 : 07:39:18

Currently I have a query....

SELECT * FROM costcentres
WHERE costcentre in
(SELECT costcentre FROM costcentres
WHERE archived = 1 GROUP BY costcentre HAVING (COUNT(costcentre) > 1 ))
AND archived = 1

Which returns all instances of a duplicate costcentre. (I'm open to suggestions as to whether this is the most efficient method....)

I'm trying to develop the query further so I can return the records highlighted in red that have both COUNT(costcentre) > 1 and COUNT(EN) > 1

*EDIT tried adding AND COUNT(EN) > 1 but it just returns the same results.

Any ideas on how this might be achieved?

Thanks in advance,

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 08:06:22

Something like this?



Select a.* From Costcenters a 
                    INNER JOIN (Select CostCentre,Count(*)
                                FROM CostCentres Where Archived = 1
                                Group by CostCentre) b
         on a.CostCentre = b.CostCentre

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-14 : 08:12:48

quote:

Originally posted by dataguru1971

Something like this?



Select a.* From Costcenters a 
                    INNER JOIN (Select CostCentre,Count(*) as counting
                                FROM CostCentres Where Archived = 1
                                Group by CostCentre) b
         on a.CostCentre = b.CostCentre

Poor planning on your part does not constitute an emergency on my part.

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 08:31:35

You have to put another Archived in the outer query.
This is becacuse same cost center can be both archive and not archived!

And to incorprate OP second whish,

SELECT	{column name list here}
FROM	(
		SELECT		{column name list here},
				COUNT(*) OVER (PARTITION BY CostCentre, EN) AS Items
		FROM		CostCentres
		WHERE		Archived = 1
	) AS d
WHERE	Items > 1

SELECT		a.*
FROM		CostCentres AS a
INNER JOIN	(
			SELECT		CostCentre,
					EN
			FROM		CostCentres
			WHERE		Archived = 1
			GROUP BY	CostCentre,
					EN
			HAVING		COUNT(*) > 1
		) AS b ON b.CostCentre = a.CostCentre AND b.EN = a.EN
WHERE		a.Archived = 1

E 12°55'05.25"
N 56°04'39.16"

konzy_malone
Starting Member

2 Posts

Posted - 2007-11-14 : 09:25:26

Cheers Peso.

Has worked a treat ta.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources