How to count columns in a single query?

• Question

• Hello, I could use some help..

I have a table (from PBX) that shows the agent name and if they took an inbound or made an outbound phone call. In this case the column "Inbound" is simply a bit field:

Agent   inbound

Bob          1

Peter        0

Paul         1

Bob         0

Peter       0

I am looking to get a query that would show:

Agent    Inbound     Outbound

Bob          1              1

Peter        0              2

Paul         1              0

Thanks for any help!

• Edited by Monday, November 12, 2018 10:55 AM
Monday, November 12, 2018 10:55 AM

• You can solve it with a conditional sum, like

```SELECT Agent,
SUM(case when inbound = 1 THEN 1 ELSE 0 END) AS Inbound,
SUM(case when inbound = 0 THEN 1 ELSE 0 END) AS Outbound
FROM yourTable
GROUP BY Agent```

Olaf Helper

[ Blog] [ Xing] [ MVP]

• Marked as answer by Tuesday, November 13, 2018 11:31 AM
Monday, November 12, 2018 11:17 AM

All replies

• You can solve it with a conditional sum, like

```SELECT Agent,
SUM(case when inbound = 1 THEN 1 ELSE 0 END) AS Inbound,
SUM(case when inbound = 0 THEN 1 ELSE 0 END) AS Outbound
FROM yourTable
GROUP BY Agent```

Olaf Helper

[ Blog] [ Xing] [ MVP]

• Marked as answer by Tuesday, November 13, 2018 11:31 AM
Monday, November 12, 2018 11:17 AM
• Assuming what you're looking at are SSRS expressions

you can do like this

for Inbound

`=Count(IIF(Fields!Inbound.Value = 1,Fields!Agent.Value,Nothing))`

For outbound

`=Count(IIF(Fields!Inbound.Value = 0,Fields!Agent.Value,Nothing))`

The grouping should be on Agent column for the container

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Monday, November 12, 2018 3:00 PM
• Hi L Holmes

As olaf and visakh said , you could do it in sql query or in design level.

Below is the detail steps for design level: dataset query:

Report structure : group on Agent

Expression :

Inbound: =sum(iif(Fields!inbound.Value=1,1,0))

Outbound: =sum(iif(Fields!inbound.Value=0,1,0))

Result:

Best Regards,

Eric Liu

Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

Tuesday, November 13, 2018 2:10 AM
• EXACTLY what I was looking for; works perfect.

Thanks,

L Holmes

Tuesday, November 13, 2018 11:32 AM
• EXACTLY what I was looking for; works perfect.

Thanks,

L Holmes

You posted this in SSRS forum thats why I provided you with SSRS expressions for the same

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog