count in sql server

Question

count in sql server

Darrell Austin

SSC Journeyman

Points: 94
More actions
March 26, 2007 at 8:05 am

#175761

I have query below in a stored procedure and it works but what I also want is count columns for direction='INBOUND' and direction='OUTBOUND' I'm not sure how to add that to this query. Obviously, count(*) just gives me the count for rows but that would include both 'INBOUND' and 'OUTBOUND'. Any ideas?

SELECT employeevoip ,
SUM(CASE WHEN Direction = 'INBOUND' THEN hours ELSE 0 END) AS 'InboundHours',
SUM(CASE WHEN Direction = 'INBOUND' THEN minutes ELSE 0 END) AS 'InboundMinutes',
SUM(CASE WHEN Direction = 'INBOUND' THEN seconds ELSE 0 END) AS 'InboundSeconds',
SUM(CASE WHEN Direction = 'OUTBOUND' THEN hours ELSE 0 END) AS 'OutboundHours',
SUM(CASE WHEN Direction = 'OUTBOUND' THEN minutes ELSE 0 END) AS 'OutboundMinutes',
SUM(CASE WHEN Direction = 'OUTBOUND' THEN seconds ELSE 0 END) AS 'OutboundSeconds'
FROM
[NPC].[dbo].[PhoneLog]
where
call_date >= @StartDate and call_date <=@EndDate
group by employeevoip order by employeevoip

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

You could do a union, with the WHERE clause in each specifying if that part of the union is getting inbound or outbound.

select sum(), count()

from phonelog

where direction = 'inbound'

union

select sum(), count()

from phonelog

where direction = 'outbound'

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 2

Not sure if its a better idea or not but you could also group by direction,

SELECT sum/count etc FROM phonelog GROUP BY direction;

EDIT: *cough* this wont work if you want each record to have a count it would give you a total count/employee count if you group by employee as well

EDIT2: Looking again it could be made to work - bah - I'm ill and not thinking straight

--
James Moore
Red Gate Software Ltd

Darrell Austin SSC Journeyman Points: 94 More actions · Answer 3

I'm not sure that will work because I want seperate columns for the inbound and outbound hours/minutes/seconds along with columns for #inbound calls and #outbound calls all in the same rown.

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 4

Do you want the #inbound #outbound calls to be cumulative? Can you give us an example results set?

- James

--
James Moore
Red Gate Software Ltd

Darrell Austin SSC Journeyman Points: 94 More actions · Answer 5

Name	InHours	InMinutes	InSeconds	NumInCalls	OutHours	OutMinutes	OutSeconds	NumOutCalls
John Smith	2	45	30	6	4	20	10	20
Jim John	1	35	2	4	33	20	10	14

This is the resultset I am looking for.

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 6

OK,

A variation on Steve's way is probably best then,

Somthing like:

SELECT * FROM

(SELECT a.employeevoip, SUM(a.hours) [InboundHours], SUM(a.mins) [InboundMins], SUM(a.seconds) [InboundSecs], COUNT(*) [IncomingCount] FROM [phonelog] a WHERE a.direction = 'INCOMING' GROUP BY a.employeevoip) incoming

LEFT JOIN

(SELECT b.employeevoip, SUM(b.hours) [OutboundHours], SUM(b.mins) [OutboundMins], SUM(b.seconds) [OutboundSecs], COUNT(*) [OutboundCount] FROM [phonelog] b WHERE b.direction = 'OUTGOING' GROUP BY b.employeevoip) outbound

WHERE incoming.employeevoip = outbound.employeevoip;

IE: Bould incoming calls with count and outgoing calls with count then join them to get your desired results set.

- James

--
James Moore
Red Gate Software Ltd

Darrell Austin SSC Journeyman Points: 94 More actions · Answer 7

Darrell Austin

SSC Journeyman

Points: 94

March 26, 2007 at 11:33 am

#696945

okay. I will try that. Thanks.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 8

SUM

(CASE WHEN Direction = 'INBOUND' THEN 1 ELSE 0 END) AS [InboundCount],

SUM(CASE WHEN Direction = 'OUTBOUND' THEN 1 ELSE 0 END) AS [OutboundCount]

...will add counts to each result row for that grouping.

If you're looking to add columns that include the counts for the entire resultset, and you're using SQL Server 2005, throw in an OVER() clause. You could add a PARTITION BY to chop that up to finer granularity as well.

SUM

(CASE WHEN Direction = 'INBOUND' THEN 1 ELSE 0 END) OVER () AS [InboundCount],

SUM(CASE WHEN Direction = 'OUTBOUND' THEN 1 ELSE 0 END) OVER () AS [OutboundCount]

-Eddie

Eddie Wuerch
MCM: SQL

Darrell Austin SSC Journeyman Points: 94 More actions · Answer 9

That worked great. Thanks. Any good Transact-SQL books you would recommend?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

SELECT employeevoip ,

SUM(CASE WHEN Direction = 'INBOUND' THEN hours ELSE 0 END) AS 'InboundHours',

SUM(CASE WHEN Direction = 'INBOUND' THEN minutes ELSE 0 END) AS 'InboundMinutes',

SUM(CASE WHEN Direction = 'INBOUND' THEN seconds ELSE 0 END) AS 'InboundSeconds',

SUM(CASE WHEN Direction = 'OUTBOUND' THEN hours ELSE 0 END) AS 'OutboundHours',

SUM(CASE WHEN Direction = 'OUTBOUND' THEN minutes ELSE 0 END) AS 'OutboundMinutes',

SUM(CASE WHEN Direction = 'OUTBOUND' THEN seconds ELSE 0 END) AS 'OutboundSeconds',

SUM(CASE WHEN Direction = 'INBOUND' THEN 1 ELSE 0 END) AS 'InboundCount',

SUM(CASE WHEN Direction = 'OUTBOUND' THEN 1 ELSE 0 END) AS 'OutboundCount'

FROM

[NPC].[dbo].[PhoneLog]

where

call_date >= @StartDate and call_date <= @EndDate

group by employeevoip order by employeevoip

N 56°04'39.16"
E 12°55'05.25"