counting values in aresult set

Hi,
I have the following query:
SELECT ahd.call_req.ref_num AS Incident
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
which returns a single column called Incidents containing integer values.
4323
4316
4316
4309
4309
4309
What I want is a list of the Integer values and the coresponding number of
times
it appears in the result set.For example;
4323 1
4316 2
4309 3
rory
I forot to say thank you in advance for any help!
Best wishes
rory
"Rory" wrote:

> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory
|||this is untested but try
SELECT ahd.call_req.ref_num AS Incident, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by Incident
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rory" <rory@.discussions.microsoft.com> wrote in message
news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number
> of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory
|||Hi Hilary,
Thanks for your reply, unfortunately it doesn't work. Back to
the "drawing board" for me I'm afraid!
Thanks again
rory
"Hilary Cotter" wrote:

> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
>
>
|||Hi Hilary,
I modified the query slightly from your suggestion( just column
names) and hey presto! Success!
Thanks you've made it a very nice Friday for me! Thanks again.
SELECT ahd.call_req.ref_num, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
rory
"Hilary Cotter" wrote:

> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
>
>
|||Rory,
Change the "group by" clause and use the correct column.
SELECT
ahd.call_req.ref_num AS Incident, count(*)
FROM
ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
go
AMB
"Rory" wrote:
[vbcol=seagreen]
> Hi Hilary,
> Thanks for your reply, unfortunately it doesn't work. Back to
> the "drawing board" for me I'm afraid!
> Thanks again
> --
> rory
>
> "Hilary Cotter" wrote:
|||Hi Alejandro,
I modified the query and it works fine,
SELECT ahd.call_req.ref_num, count(*) AS Hops
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
thanks for your response it is very much appreciated.
rory
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Rory,
> Change the "group by" clause and use the correct column.
> SELECT
> ahd.call_req.ref_num AS Incident, count(*)
> FROM
> ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by ahd.call_req.ref_num
> go
>
> AMB
>
> "Rory" wrote: