Getting count of a grouping - 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

General SQL Server Forums

New to SQL Server Programming

Getting count of a grouping

Author

Topic

mnolting
Starting Member

36 Posts

Posted - 2009-10-02 : 23:21:53

I have a database with a field named "Agent". I have a query that counts the number of times each agent exists in the table (reports):

select count(*)as 'No. of Times', agent from reports group by agent order by count(*) desc, agent.

The result looks like this:

No. of Times agent
28 Bob Smith
22 John Brown
22 kay keck
15 ken black
1 lisa scottline
1 laura dern

(and so on)
I also need to know how many "1's" I have (which would be another query). I would also like to modify my query to say "where No. of Times" > 5 and then have a total of the "Counts" (which would be 87 in the above example)
Thanks a million

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-02 : 23:45:42

quote:
"I also need to know how many "1's" I have"[/quote[

select count(*)as 'No. of Times', agent from reports group by agent having count(*) = 1 order by agent

[quote]"I would also like to modify my query to say "where No. of Times" > 5 and then have a total of the "Counts" (which would be 87 in the above example)"


select count(*)as 'No. of Times', agent, t.total_count
from   reports 
       cross join
       (
           select count(*) as total_count
           from   reports
       ) t
group by agent 
having count(*) > 5 
order by agent

KH
[spoiler]Time is always against us[/spoiler]

mnolting
Starting Member

36 Posts

Posted - 2009-10-03 : 01:15:11

Thanks Khtan, but I am looking for the sum of Just the results returned, I am getting the value of the total number of records (which is 695)

The 1's works, I should of thought of that.
Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-03 : 12:09:30

are you using SQL 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-03 : 12:13:30

[code]
SELECT COUNT(*)AS 'No. of Times', agent, t.total_count
FROM reports
CROSS JOIN
(
SELECT total_count = SUM(cnt)
FROM
(
SELECT cnt = COUNT(*), agent
FROM reports
GROUP BY agent
HAVING COUNT(*) > 5
) c
) t
GROUP BY agent
HAVING COUNT(*) > 5
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-03 : 12:16:29

try this also if you are using SQL 2005 / 2008


-- SQL 2005 / 2008 only
SELECT COUNT(*)AS 'No. of Times', agent, 
	total_count = SUM( COUNT(*) ) OVER ()
FROM   reports 
GROUP BY agent 
HAVING COUNT(*) > 5

KH
[spoiler]Time is always against us[/spoiler]

mnolting
Starting Member

36 Posts

Posted - 2009-10-04 : 00:18:52

I encounter "Error: no such column: cnt (1)"

with the query for the post on 10/03/09

Mike

mnolting
Starting Member

36 Posts

Posted - 2009-10-04 : 00:51:42

Khtan,
The following change works, but I needed it sorted and it does not sort.

SELECT COUNT(*)AS 'No. of Times', agent, t.total_count
FROM reports
CROSS JOIN
(
SELECT SUM(cnt) as total_count
FROM
(
SELECT COUNT(reportid) as cnt, agent
FROM reports
GROUP BY agent
HAVING COUNT(*) > 5 order by 'No. of Times'
) c
) t
GROUP BY agent
HAVING COUNT(*) > 5 order by 'No. of Times'

Thanks
Mike

mnolting
Starting Member

36 Posts

Posted - 2009-10-04 : 00:55:23

Khtan

OK the following works. You cant "order by" on a column name in quotes.

SELECT COUNT(*)AS nn, agent, t.total_count
FROM reports
CROSS JOIN
(
SELECT SUM(cnt) as total_count
FROM
(
SELECT COUNT(*) as cnt, agent
FROM reports
GROUP BY agent
HAVING COUNT(*) > 5 and agent not null
) c
) t
GROUP BY agent
HAVING COUNT(*) > 5 and agent not null order by nn desc

How about the number of "1's". That is, the number of records where an agent name exists only in one record?
Thanks
Mike
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-04 : 05:18:39

quote:
Originally posted by mnolting

I encounter "Error: no such column: cnt (1)"

with the query for the post on 10/03/09

Mike

which query is this ? can you post the query you used here ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-04 : 05:20:15

quote:

Originally posted by mnolting

Khtan,
The following change works, but I needed it sorted and it does not sort.


SELECT COUNT(*)AS 'No. of Times', agent, t.total_count
FROM   reports 
       CROSS JOIN
       (
           SELECT SUM(cnt) as total_count
           FROM
           (
	           SELECT  COUNT(reportid) as cnt, agent
	           FROM   reports
	           GROUP BY agent
	           HAVING COUNT(*) > 5 order by 'No. of Times'
	   ) c
       ) t
GROUP BY agent 
HAVING COUNT(*) > 5 
order by 'No. of Times'

Thanks
Mike

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-04 : 05:23:35

quote:

Originally posted by mnolting

Khtan

OK the following works. You cant "order by" on a column name in quotes.


SELECT COUNT(*) AS [No. of Times], agent, t.total_count
FROM   reports 
       CROSS JOIN 
       (
           SELECT SUM(cnt) as total_count
           FROM
           (
	           SELECT  COUNT(*) as cnt, agent
	           FROM   reports
	           GROUP BY agent
	           HAVING COUNT(*) > 5 and agent not null
	   ) c
       ) t
GROUP BY agent 
HAVING COUNT(*) > 5 and agent not null 
order by [No. of Times] desc

How about the number of "1's". That is, the number of records where an agent name exists only in one record?
Thanks
Mike
Thanks

"You cant "order by" on a column name in quotes."
use bracket [ ] to enclosed the alias name

"What about number of "1's" ?
what do you want to do about those records ?

KH
[spoiler]Time is always against us[/spoiler]

mnolting
Starting Member

36 Posts

Posted - 2009-10-04 : 23:38:44

Khtan

Thanks
The brackets work.
I figured out the other question.

Thanks very much
Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources