COUNT problem

Question

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: 184394 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

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

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: 184394 More actions · Answer 4

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

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

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

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 7654, 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

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: 184394 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]