SQL Multiple count columns with multiple conditionS

Question

I am trying to gather some basic statistics from a table "Data_Table" that gets updated on a daily basis. Each row represents a case, which can be opened/closed/cancelled by an operator with a unique ID. I want to be able to show the count for the actions that each operator did the previous day. So getting from Data_Table to Ideal table.

Data_Table

|  LOCATION |   DATE   | REFERENCE | OPENED_ID | CLOSED_ID | CANCELLED_ID  |
|    NYC    | 20180102 |  123451   |    123    |    234    |      0        |
|    TEX    | 20180102 |  123452   |    345    |    123    |      0        |
|    NYC    | 20180102 |  123453   |    345    |     0     |     123       |
|    TEX    | 20180102 |  123453   |    234    |     0     |     123       |

Ideal Table

|  LOCATION |   DATE   | USER_ID | OPEN | CLOSED | CANCELLED  |
|    NYC    | 20180102 |  123    |  1   |   0    |     1      |  
|    NYC    | 20180102 |  234    |  0   |   1    |     0      |
|    NYC    | 20180102 |  345    |  1   |   0    |     0      |
|    TEX    | 20180102 |  123    |  0   |   1    |     1      |
|    TEX    | 20180102 |  234    |  1   |   0    |     0      |
|    TEX    | 20180102 |  345    |  1   |   0    |     0      |

User 123 opened 1 case and cancelled 1 case in location NYC on date 20180102...etc.

I have made a few small queries for each action in each site that looks like this:

SELECT LOCATION, DATE, OPENED_ID, COUNT(DISTINCT [DATA_TABLE].REFERENCE)
FROM [DATA_TABLE]
WHERE DATE = CONVERT(DATE,GETDATE()-1)
AND LOCATION = 'NYC'
AND OPENED_ID in (SELECT NYC FROM [OP_ID_TABLE]WHERE [DATE FINISH] > GETDATE() )
GROUP BY OPENED_ID, LOCATION, DATE
ORDER BY LOCATION

And then repeat this query for each location for each operator action. After which I do some messy vlookups in excel to organise it into the Ideal table format, which on a daily basis is ..not ideal.

I've tried to make some sum functions but haven't had any luck.

Any help would be much appreciated.

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2018-04-06 14:53:38 +00:00
Commented Apr 6, 2018 at 14:53 — Gordon Linoff
– Gordon Linoff, Commented Apr 6, 2018 at 14:53

Gordon Linoff · Accepted Answer · 2018-04-06 14:53:42Z

2

You need to unpivot and re-aggregate. One method uses union all and group by:

select location, date, user_id,
       sum(opened) as opens, sum(closed) as closes, sum(cancelled) as cancels
from ((select location, date, opened_id as user_id, 1 as opened, 0 as closed, 0 as cancelled
       from t
      ) union all
      (select location, date, closed_id as user_id, 0 as opened, 1 as closed, 0 as cancelled
       from t
      ) union all
      (select location, date, cancelled_id as user_id, 0 as opened, 0 as closed, 1 as cancelled
       from t
      )
     ) t
group by location, date, user_id;

There are other methods for doing these operations, depending on the database. However, this is ANSI-standard syntax.

answered Apr 6, 2018 at 14:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Multiple count columns with multiple conditionS

Data_Table

Ideal Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Data_Table

Ideal Table

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related