|
Home > Archive > MS SQL Server > October 2006 > counting values in aresult set
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Hilary Cotter 2006-10-24, 6:37 pm |
| 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@microso
ft.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@microso
ft.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@microso
ft.com...
>
>
>
| |
| Alejandro Mesa 2006-10-24, 6:37 pm |
| 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:
[color=darkred]
> 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:
[color=darkred]
> 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:
>
|
|
|
|
|