CODEWITHSUNDEEP
sqlsql-servert-sql
Hakan Zim
Hakan Zim

Reputation: 305

Multiple 'group by' from a subquery

The below query comes from a view. Case is used because the patrol number comes from different fields based on name value.

sub Query

select timetag1, name,   
case when name = 'Location1' then afield17
     when name = 'Location2' then afield16 
     when name = 'Location3' then afield22 end as PatrolNo
FROM         someView
where timetag1 > (getdate-2) and timetag1 < GETDATE()
group by PatrolNo, Name

The above query gives me the following results of three locations and patrol numbers for last two days. Result:

timetag1                        name                    PatrolNo


2014-07-19 17:53:22.000         Location1               A

2014-07-19 17:54:12.000         Location1               B

2014-07-19 17:55:08.000         Location2               B

2014-07-19 17:55:38.000         Location3               C

2014-07-19 17:56:13.000         Location2               A

2014-07-19 18:59:01.000         Location1               C

2014-07-19 19:59:45.000         Location2               B

2014-07-18 13:00:28.000         Location1               B

2014-07-18 13:02:47.000         Location3               A

2014-07-18 13:03:45.000         Location1               B

2014-07-18 14:04:46.000         Location2               C

2014-07-18 15:05:37.000         Location2               B

2014-07-18 18:06:25.000         Location1               A

2014-07-18 18:08:02.000         Location3               C

now i want to use the above query as a sub query (since it already comes from the view i dont want to have nested view) so i could get count of patrol numbers for a given location(name) at a given date

its like group by date then group by location against the count of patrol number.

for instance if you take the first row from below for the day of 19th location1 had 1 patrol number with the value of A

Expected 

name        timetag1        PatrolNo     Count

Location1   2014-07-19          A            1

Location1   2014-07-19          B            1

Location1   2014-07-19          C            1

Location2   2014-07-19          A            1

Location2   2014-07-19          B            2

Location3   2014-07-19          C            1

Location1   2014-07-18          A            1

Location1   2014-07-18          B            2

Location2   2014-07-18          C            1

Location2   2014-07-18          B            1

Location3   2014-07-18          A            1

Location3   2014-07-18          C            1

Unsuccessful Attempt :

select temp.timetag1, temp.PatrolNo, COUNT(*) from

(select timetag1, name,   
case when name = 'Location1' then afield17
     when name = 'Location2' then afield16 
     when name = 'Location3' then afield22 end as PatrolNo
FROM         someView
where timetag1 > (getdate-2) and timetag1 < GETDATE()) as temp

group by timetag1, name, PatrolNo

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You are very close:

select cast(temp.timetag1 as date), temp.PatrolNo, COUNT(*)
from (select timetag1, name,   
             (case when name = 'Location1' then afield17
                   when name = 'Location2' then afield16 
                   when name = 'Location3' then afield22
              end) as PatrolNo
      FROM  someView
      where timetag1 > (getdate() - 2) and timetag1 < GETDATE()
    ) temp
group by cast(temp.timetag1 as date), PatrolNo;

The major change was to remove name() from the group by clause. In addition, I assume the first mention of getdate() is a function call, so I added parens.

Upvotes: 1

Related Questions