COUNT problem

Question

Post reply

COUNT problem

adam-639168

Default port

Points: 1444
More actions
July 29, 2008 at 10:39 am

#188810

I have the following query:

select

account,

count(rank) [count]

from dxhistory

where rank=1

group by account, rank

having count(rank)>1

which returns accounts that have more than one rank of '1'.

e.g.

ACCOUNT COUNT

9021 2

7654 5

7938 3

I need a second query that counts how many accounts are returned (in my example above it would be 3).

I can manually count the number of rows returned but was hoping there is a better way of counting this.

Thanks for your help.

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

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

You can immediately follow your first query with Select @@ROWCOUNT which returns the # of rows returned by the previous statement.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

adam-639168 Default port Points: 1444 More actions · Answer 2

adam-639168

Default port

Points: 1444

July 29, 2008 at 10:46 am

#849983

great, that's really useful to know.

Thanks.

Jack Corbett (7/29/2008)

You can immediately follow your first query with Select @@ROWCOUNT which returns the # of rows returned by the previous statement.

adam-639168 Default port Points: 1444 More actions · Answer 3

adam-639168

Default port

Points: 1444

July 29, 2008 at 12:00 pm

#850030

how would I use this if I wanted to use the following:

declare @count int

set @count = ?

if @count>=1

Jack Corbett (7/29/2008)

You can immediately follow your first query with Select @@ROWCOUNT which returns the # of rows returned by the previous statement.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 4

Jack Corbett

SSC Guru

Points: 184388

July 29, 2008 at 12:07 pm

#850041

Select @Count = @@ROWCOUNT

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Zahran SSCommitted Points: 1724 More actions · Answer 5

Zahran

SSCommitted

Points: 1724

July 29, 2008 at 1:44 pm

#850100

You can use this query too...

SELECT



COUNT (DISTINCT ACCOUNT)

FROM dxhistory

WHERE RANK=1

GROUP BY ACCOUNT, RANK

HAVING COUNT(RANK)>1

- Zahran -

adam-639168 Default port Points: 1444 More actions · Answer 6

adam-639168

Default port

Points: 1444

July 29, 2008 at 1:54 pm

#850108

this returns rows of 1's

Zahran (7/29/2008)

You can use this query too...

SELECT
COUNT (DISTINCT ACCOUNT) FROM dxhistory WHERE RANK=1 GROUP BY ACCOUNT, RANK
HAVING COUNT(RANK)>1

Zahran SSCommitted Points: 1724 More actions · Answer 7

Zahran

SSCommitted

Points: 1724

July 29, 2008 at 10:43 pm

#850255

Try this,

SELECT COUNT(ACCOUNT) FROM



(SELECT COUNT(DISTINCT ACCOUNT) AS ACCOUNT

FROM dxhistory

WHERE RANK=1

GROUP BY ACCOUNT, RANK

HAVING COUNT(RANK)>1)AS T

- Zahran -

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Hi Adam

Here's a little extra material relating to your question. It pulls in Jack's nifty solution which would appear to be ideal in your case, and an alternative method or two for your perusal.

DROP TABLE #dxhistory



SET NOCOUNT ON

CREATE TABLE #dxhistory (account INT, rank INT)

INSERT INTO #dxhistory (account, rank)

SELECT 9021, 1 UNION ALL

SELECT 9021, 1 UNION ALL

SELECT 7654, 1 UNION ALL

SELECT 7654, 1 UNION ALL

SELECT 7654, 1 UNION ALL

SELECT 7654, 1 UNION ALL

SELECT 7654, 1 UNION ALL

SELECT 7938, 1 UNION ALL

SELECT 7938, 1 UNION ALL

SELECT 7938, 1 UNION ALL

SELECT 7939, 1 UNION ALL

SELECT 7940, 1 UNION ALL

SELECT 7941, 1 UNION ALL

SELECT 7942, 1 UNION ALL

SELECT 7943, 1 

DECLARE @Count INT -- must be before the select

-- returns accounts that have more than one rank of '1'

-- this is functionally equivalent to the original query

SELECT account, COUNT(*) AS [count]

FROM #dxhistory

WHERE rank = 1

GROUP BY account --, rank not strictly necessary 'cos there's only one

HAVING COUNT(*) > 1

SELECT @Count = @@ROWCOUNT -- because @@ROWCOUNT applies to the last statement

PRINT 'From @@ROWCOUNT: ' + CAST(@Count AS CHAR(1))

-- could also use this, which uses the original WORKING query as a derived table

-- but means the original query runs twice (assuming it's run once to collect the affected account numbers)

SELECT @Count = COUNT(*)

FROM (SELECT account, count(rank) AS [count]

	FROM #dxhistory

	WHERE rank=1

	GROUP BY account --, rank not necessary

	HAVING count(*)>1) d

PRINT 'From SELECT: ' + CAST(@Count AS CHAR(1))

--===============================================================================

-- You could also run the results into a temporary table:

SELECT account, COUNT(*) AS [count]

INTO #dxhistory_temp

FROM #dxhistory

WHERE rank = 1

GROUP BY account --, rank not strictly necessary 'cos there's only one

HAVING COUNT(*) > 1

-- then, getting the account numbers is straightforward:

SELECT * FROM #dxhistory_temp

-- and getting the number of accounts affected is also straightforward:

SELECT COUNT(*) FROM #dxhistory_temp

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

adam-639168 Default port Points: 1444 More actions · Answer 9

adam-639168

Default port

Points: 1444

August 18, 2008 at 1:30 pm

#859634

thank you; this gave me exactly what I need

Zahran (7/29/2008)

Try this,

SELECT COUNT(ACCOUNT) FROM
(SELECT COUNT(DISTINCT ACCOUNT) AS ACCOUNT FROM dxhistory WHERE RANK=1 GROUP BY ACCOUNT, RANK
HAVING COUNT(RANK)>1)AS T

adam-639168 Default port Points: 1444 More actions · Answer 10

thank you for the detailed response, I really appreciate it. Very informative I had thought about a temporary table also but your explanation clarified exactly how to do it.

Chris Morris (7/30/2008)

Hi Adam

Here's a little extra material relating to your question. It pulls in Jack's nifty solution which would appear to be ideal in your case, and an alternative method or two for your perusal.

DROP TABLE #dxhistory
SET NOCOUNT ON CREATE TABLE #dxhistory (account INT, rank INT) INSERT INTO #dxhistory (account, rank) SELECT 9021, 1 UNION ALL SELECT 9021, 1 UNION ALL SELECT 7654, 1 UNION ALL SELECT 7654, 1 UNION ALL SELECT 7654, 1 UNION ALL SELECT 7654, 1 UNION ALL SELECT 7654, 1 UNION ALL SELECT 7938, 1 UNION ALL SELECT 7938, 1 UNION ALL SELECT 7938, 1 UNION ALL SELECT 7939, 1 UNION ALL SELECT 7940, 1 UNION ALL SELECT 7941, 1 UNION ALL SELECT 7942, 1 UNION ALL SELECT 7943, 1 DECLARE @Count INT -- must be before the select -- returns accounts that have more than one rank of '1' -- this is functionally equivalent to the original query SELECT account, COUNT(*) AS [count] FROM #dxhistory WHERE rank = 1 GROUP BY account --, rank not strictly necessary 'cos there's only one HAVING COUNT(*) > 1 SELECT @Count = @@ROWCOUNT -- because @@ROWCOUNT applies to the last statement PRINT 'From @@ROWCOUNT: ' + CAST(@Count AS CHAR(1)) -- could also use this, which uses the original WORKING query as a derived table -- but means the original query runs twice (assuming it's run once to collect the affected account numbers) SELECT @Count = COUNT(*) FROM (SELECT account, count(rank) AS [count] FROM #dxhistory WHERE rank=1 GROUP BY account --, rank not necessary HAVING count(*)>1) d PRINT 'From SELECT: ' + CAST(@Count AS CHAR(1)) --=============================================================================== -- You could also run the results into a temporary table: SELECT account, COUNT(*) AS [count] INTO #dxhistory_temp FROM #dxhistory WHERE rank = 1 GROUP BY account --, rank not strictly necessary 'cos there's only one HAVING COUNT(*) > 1 -- then, getting the account numbers is straightforward: SELECT * FROM #dxhistory_temp -- and getting the number of accounts affected is also straightforward: SELECT COUNT(*) FROM #dxhistory_temp

Cheers

ChrisM

Jack Corbett SSC Guru Points: 184388 More actions · Answer 11

I don't think Chris was suggesting you use a temporary table, I think he was just using a temporary table to hold the test data so he could give a tested solution.

This is actually a recommended method of posting a question. If you check the links in my signature you will see that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Note that this works also and requires only one SELECT:



SELECT account

 , COUNT(*) AS [count]

 , Count(*) Over(Partition By 1) as [Total_Groups]

FROM #dxhistory

WHERE rank = 1

GROUP BY account

HAVING COUNT(*) > 1

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]