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
--
roryI 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 that, unortunately it does not work.
Regards,
--
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,
> > 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,
> > 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,
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...
> > 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
>
>|||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:
> 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,
> > > 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 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:
> 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:
> > 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,
> > > > 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
> > >
> > >
> > >