locked
How to count columns in a single query? RRS feed

  • 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 L Holmes Monday, November 12, 2018 10:55 AM
    Monday, November 12, 2018 10:55 AM

Answers

  • 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 L Holmes 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 L Holmes 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
    My Facebook Page

    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:

    Hope it can help you.

    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
    My Facebook Page

    Tuesday, November 13, 2018 11:40 AM