Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Select на сбор данных  [new]
shoh
Member

Откуда:
Сообщений: 158
помогите решить задание
нужно в результат добавить еще одну строку Total:
прилагаю селект ктр уже работает и выдает след. результат:

Severity January February March April May June July August September October November December total
--------------------------------------------------------------------------------------------------------------------------------------------------------
Critical 1 2 1 1 0 1 0 0 2 0 2 2 12
Major 9 6 9 4 6 10 5 9 5 3 1 6 73
Minor 4 2 4 3 0 1 4 5 3 4 7 1 38

а нужно еще добавить строку :

Severity January February March April May June July August September October November December total
--------------------------------------------------------------------------------------------------------------------------------------------------------
Critical 1 2 1 1 0 1 0 0 2 0 2 2 12
Major 9 6 9 4 6 10 5 9 5 3 1 6 73
Minor 4 2 4 3 0 1 4 5 3 4 7 1 38
--------------------------------------------------------------------------------------------------------------------------------------------------------
Total 14 10 14 8 6 11 9 14 10 7 10 9 123

----------------------------------------------------------------

вот селект, надо сюда добавить опцию


select b.severity1,
sum(case when b.mounth1 = '1' then b.test else 0 end) as January,
sum(case when b.mounth1 = '2' then b.test else 0 end) as February,
sum(case when b.mounth1 = '3' then b.test else 0 end) as March,
sum(case when b.mounth1 = '4' then b.test else 0 end) as April,
sum(case when b.mounth1 = '5' then b.test else 0 end) as May,
sum(case when b.mounth1 = '6' then b.test else 0 end) as June,
sum(case when b.mounth1 = '7' then b.test else 0 end) as July,
sum(case when b.mounth1 = '8' then b.test else 0 end) as August,
sum(case when b.mounth1 = '9' then b.test else 0 end) as September,
sum(case when b.mounth1 = '10' then b.test else 0 end) as October,
sum(case when b.mounth1 = '11' then b.test else 0 end) as November,
sum(case when b.mounth1 = '12' then b.test else 0 end) as December,
sum(b.test) total
from (
select severity as severity1, (DATEPART (month,created)) as mounth1, count(*) test from sr
where status in ('closed')
and (created between '01/01/2013' and '31/12/2013' )
group by severity, (DATEPART (month,created))
) b group by b.severity1



спасибо
28 ноя 14, 09:34    [16917221]     Ответить | Цитировать Сообщить модератору
 Re: Select на сбор данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
select b.severity1,
sum(case when b.mounth1 = '1' then b.test else 0 end) as January, 
sum(case when b.mounth1 = '2' then b.test else 0 end) as February,
sum(case when b.mounth1 = '3' then b.test else 0 end) as March,
sum(case when b.mounth1 = '4' then b.test else 0 end) as April,
sum(case when b.mounth1 = '5' then b.test else 0 end) as May,
sum(case when b.mounth1 = '6' then b.test else 0 end) as June,
sum(case when b.mounth1 = '7' then b.test else 0 end) as July,
sum(case when b.mounth1 = '8' then b.test else 0 end) as August,
sum(case when b.mounth1 = '9' then b.test else 0 end) as September,
sum(case when b.mounth1 = '10' then b.test else 0 end) as October,
sum(case when b.mounth1 = '11' then b.test else 0 end) as November,
sum(case when b.mounth1 = '12' then b.test else 0 end) as December,
sum(b.test) total 
from (
select severity as severity1, (DATEPART (month,created)) as mounth1, count(*) test from sr 
where status in ('closed')
and (created between '01/01/2013' and '31/12/2013' )
group by severity, (DATEPART (month,created))
) b group by b.severity1 with rollup
28 ноя 14, 09:36    [16917228]     Ответить | Цитировать Сообщить модератору
 Re: Select на сбор данных  [new]
shoh
Member

Откуда:
Сообщений: 158
RКлассно ! Работает
Молодец
Спасибо огромное

только еще один момент данная поля названа как NULL
можно ли как то дать имя
типа as test
28 ноя 14, 09:46    [16917274]     Ответить | Цитировать Сообщить модератору
 Re: Select на сбор данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
http://msdn.microsoft.com/ru-ru/library/ms178544(v=sql.110).aspx
28 ноя 14, 09:50    [16917291]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить