CODEWITHSUNDEEP
sqlsql-server
tom2490
tom2490

Reputation: 35

SQL Server - COUNT with GROUP BY in subquery

I have been really struggling with this one! Essentially, I have been trying to use COUNT and GROUP BY within a subquery, errors returning more than one value and whole host of errors.

So, I have the following table:

start_date  | ID_val     |  DIR   | tsk | status|
-------------+------------+--------+-----+--------+
25-03-2015  |   001      |   U    | 28  |   S    |
27-03-2016  |   003      |   D    | 56  |   S    |
25-03-2015  |   004      |   D    | 56  |   S    |
25-03-2015  |   001      |   U    | 28  |   S    |
16-02-2016  |   002      |   D    | 56  |   S    |
25-03-2015  |   001      |   U    | 28  |   S    |
16-02-2016  |   002      |   D    | 56  |   S    |
16-02-2016  |   005      |   NULL | 03  |   S    |
25-03-2015  |   001      |   U    | 17  |   S    |
16-02-2016  |   002      |   D    | 81  |   S    |

Ideally, I need to count the number of times the unique value of ID_val had for example U and 28 or D and 56. and only those combinations.

For example I was hoping to return the below results if its possible:

start_date   | ID_val     |  no of times  | status |
-------------+------------+---------------+--------+
25-03-2015  |   001      |      3        |    S   |
27-03-2016  |   003      |      1        |    S   |
25-03-2015  |   004      |      1        |    S   |
25-03-2015  |   002      |      3        |    S   |

I've managed to get the no of times on their own, but not be apart of a table with other values (subquery?)

Any advice is much appreciated!

Upvotes: 2

Views: 114

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is a basic conditional aggregation:

select id_val,
       sum(case when (dir = 'U' and tsk = 28) or (dir = 'D' and tsk = 56)
                then 1 else 0
           end) as NumTimes
from t
group by id_val;

I left out the other columns because your question focuses on id_val, dir, and tsk. The other columns seem unnecessary.

Upvotes: 2

Steve Mangiameli
Steve Mangiameli

Reputation: 688

So far, all the answers assume you are going to know the value pairs in advance and will require modification if these change or are added to. This solution makes no assumptions.

Table Creation

CREATE TABLE IDCounts
(
      start_date date
    , ID_val     char(3)
    , DIR        nchar(1)
    , tsk        int
    , status     nchar(1)
)

INSERT IDCounts
VALUES
     ('2015-03-25','001','U'  , 28,'S')
    ,('2016-03-27','003','D'  , 56,'S')
    ,('2015-03-25','004','D'  , 56,'S')
    ,('2015-03-25','001','U'  , 28,'S')
    ,('2016-03-16','002','D'  , 56,'S')
    ,('2015-03-25','001','U'  , 28,'S')
    ,('2016-02-16','002','D'  , 56,'S')
    ,('2016-02-16','005', NULL, 03,'S')
    ,('2015-03-25','001','U'  , 17,'S')
    ,('2016-02-16','002','D'  , 81,'S');

Code

SELECT Distinct i1.start_date, i1.ID_Val, i2.NumOfTimes, i1.status
from IDCounts i1
    JOIN 
    (
        select start_date, ID_val, isnull(DIR,N'')+cast(tsk as nvarchar) ValuePair, count(DIR+cast(tsk as nvarchar)) as NumOfTimes
        from IDCounts
        GROUP BY start_date, ID_val, isnull(DIR,N'')+cast(tsk as nvarchar)
    ) i2 on i2.start_date=i1.start_date
        and i2.ID_val    =i1.ID_val
        and i2.ValuePair =isnull(i1.DIR,N'')+cast(i1.tsk as nvarchar)
order by i1.ID_val, i1.start_date;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want one result per ID_val, so you'd group by ID_val.

You want the minimum start date: min(start_date).

You want any status (as it is always the same): e.g. min(status) or max(status).

You want to count matches: count(case when <match> then 1 end).

select
  min(start_date) as start_date,
  id_val,
  count(case when (dir = 'U' and tsk = 28) or (dir = 'D' and tsk = 56) then 1 end) 
    as no_of_times,
  min(status) as status
from mytable
group by id_val;

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use COUNT with GROUP BY.

Query

select start_date, ID_val, count(ID_Val) as [no. of times], [status]
from your_table_name
where (tsk = 28 and DIR = 'U') or (tsk = 56 and DIR = 'D')
group by start_date, ID_val, [status]

Upvotes: 1

Related Questions