agrupar por tipo de servico

[MICHELI_MARTINS]

gente boa tarde. estou com uma duvida aqui... é o seguinte tenho um script de sql assim:

select (s.servico), s.codigo

from servicos s, itensorcamento i, orcamentos os

where

s.codigo = i.grupo

and os.numero = i.codigoref

and cast(floor(cast(data as float)) as datetime) between '2010-03-03' and '2010-03-04'

group by s.servico, s.codigo, os.numero

order by servico

que me retorna no banco:

servico codigo

primeiro emplacamento 1

primeiro emplacamento 1

primeiro emplacamento 1

renovaçao licenca 9

acontece que eu queria contar os tipos de servico por exemplo no caso do primeiro emplacamento seriam 3 e na renovacao licença seria 1

como faço isso?

[fulvio]

Boa tarde Micheli,

Uma dúvida: o seu resultado não deveria aparecer assim não?

servico codigo

primeiro emplacamento 1

renovaçao licenca 9

Pois desta forma, se você colocar a soma em uma terceira coluna, ela será duplicada. Ficaria assim:

servico codigo SOMA

primeiro emplacamento 1 3

primeiro emplacamento 1 3

primeiro emplacamento 1 3

renovaçao licenca 9 1

Vi que no group by possui a coluna numero (os.numero). Esta coluna é realmente necessária? Deve ser por isso que as linhas (primeiro emplacamento) estão duplicadas.

Bem, caso queira contar os serviços, tente fazer desta forma:

select (s.servico), s.codigo, count(s.servico) as Soma
from servicos s, itensorcamento i, orcamentos os
where 
s.codigo = i.grupo
and os.numero = i.codigoref
and cast(floor(cast(data as float)) as datetime) between '2010-03-03' and '2010-03-04'
group by s.servico, s.codigo, os.numero
order by servico

[MICHELI_MARTINS]

o problema não é fazer o count. ate ai eu consigo fazer porem as tabelas estao alinhadas assim:

os.numero i.codigoref s.servico

uma ordem de servico possui varios itens de orcamento que pertencem ao grupo x

so que no caso eu preciso para cada ordem de servico contar apenas uma vez o servico e não todas as vezes que ele aparecer na itens de orcamento

se eu fizer o count direto ao invés de 3 primeiros emplacamentos ele vai me jogar 10 primeiros emplacamentos por causa da itens de orcamento (cada servico, no caso primeiro emplacamento possui varios subitens necessarios).

precisaria de algo como o distinct que conta apenas uma vez...

não sei se consegui te passar o problema..

[fulvio]

Rs... não sei se entendi direito, mas vamos lá:

Criei umas temporárias para tentar simular as referências entre as tabelas e seus códigos:

-- Criação

create table #Servicos (servico int, codigo int)

create table #itensorcamento (grupo int, codigoref int)

create table #orcamentos (numero int)

-- Inserção

insert into #Servicos (servico, codigo) values (1,100)

insert into #itensorcamento (grupo, codigoref) values (100,500)

insert into #orcamentos (numero) values (500)

insert into #itensorcamento (grupo, codigoref) values (100,600)

insert into #itensorcamento (grupo, codigoref) values (100,700)

insert into #orcamentos (numero) values (600)

insert into #orcamentos (numero) values (700)

Ai tentei fazer o script de modo que retorne todos os campos e apenas 1, de acordo com o "Servicos". Retirei o group by. Roda os dois scripts e olha se é isso que deseja:

select s.servico, s.codigo
from servicos s, itensorcamento i
where s.codigo = i.grupo and i.codigoref = (select top 1 it.codigoref from orcamentos orc,
itensorcamento it where orc.numero = it.codigoref and it.grupo=s.codigo)
order by servico

select s.servico, s.codigo
from servicos s, itensorcamento i
where s.codigo = i.grupo and i.codigoref in (select it.codigoref from orcamentos orc,
itensorcamento it where orc.numero = it.codigoref and it.grupo=s.codigo)
order by servico

[MICHELI_MARTINS]

fulvio o primeiro select nem traz resultado,

select s.servico, s.codigo

from servicos s, itensorcamento i,ORCAMENTOS OS

where

s.codigo = i.grupo

AND OS.NUMERO = I.CODIGOREF

AND CAST(FLOOR(CAST(DATA AS float)) AS datetime) BETWEEN '2010-03-03' AND '2010-03-04'

and i.codigoref = (select top 1 it.codigoref

from orcamentos orc,itensorcamento it

where

orc.numero = it.codigoref

and it.grupo=s.codigo)

order by servico

o segundo traz mas continua trazendo uma totalização de itens de orcamento quando na verdade quero só o servico, é como se ele contasse o grupo em 10 serviços (por exemplo) para um mesmo orcamento.

select COUNT(s.servico), s.codigo

from servicos s, itensorcamento i,ORCAMENTOS OS

where

s.codigo = i.grupo

AND OS.NUMERO = I.CODIGOREF

AND CAST(FLOOR(CAST(DATA AS float)) AS datetime) BETWEEN '2010-03-03' AND '2010-03-04'

and i.codigoref in (select it.codigoref

from orcamentos orc,itensorcamento it

where

orc.numero = it.codigoref

and it.grupo=s.codigo)

GROUP BY S.SERVICO,s.codigo

order by servico

nesse select, para o servico de codigo 1 esta trazendo 30 quando era pra trazer 3, e para o de codigo 9 esta trazendo 12 (que são seus 12 subitens), quando na verdade so existe um orçamento pra ele.

[fulvio]

Bom dia Micheli,

A estrutura do 1º select que montou está errada. Perceba que você referencia a tabela "ORCAMENTOS" no primeiro select, e eu retirei o join com esta tabela, onde eu o referencio no subselect.

Pegue o 1º select que passei e compare com o que postou logo acima.

A diferença entre os dois é exatamente isto que explicou:

- o 1º teria que retornar a quantidade de serviços, independente da quantidade de itens.

- o 2º teria que retornar a quantidade de serviços, proporcional a quantidade de itens.

Para fazer o teste rode o script:

-- Criação
create table #Servicos (servico int, codigo int)
create table #itensorcamento (grupo int, codigoref int)
create table #orcamentos (numero int)
-- Inserção de dados
insert into #Servicos (servico, codigo) values (1,100)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #orcamentos (numero) values (500)
insert into #itensorcamento (grupo, codigoref) values (100,600)
insert into #itensorcamento (grupo, codigoref) values (100,700)
insert into #orcamentos (numero) values (600)
insert into #orcamentos (numero) values (700)

-- select que retorna a quantidade de serviços
select s.servico, s.codigo
from #servicos s, #itensorcamento i
where s.codigo = i.grupo and i.codigoref = (select top 1 it.codigoref from #orcamentos orc,
#itensorcamento it where orc.numero = it.codigoref and it.grupo=s.codigo)
order by servico

-- select que retorna a quantidade de serviços, proporcional aos itens
select s.servico, s.codigo
from #servicos s, #itensorcamento i
where s.codigo = i.grupo and i.codigoref in (select it.codigoref from #orcamentos orc,
#itensorcamento it where orc.numero = it.codigoref and it.grupo=s.codigo)
order by servico

[MICHELI_MARTINS]

Bom dia Micheli,

A estrutura do 1º select que montou está errada. Perceba que você referencia a tabela "ORCAMENTOS" no primeiro select, e eu retirei o join com esta tabela, onde eu o referencio no subselect.

Pegue o 1º select que passei e compare com o que postou logo acima.

A diferença entre os dois é exatamente isto que explicou:

- o 1º teria que retornar a quantidade de serviços, independente da quantidade de itens.

- o 2º teria que retornar a quantidade de serviços, proporcional a quantidade de itens.

Para fazer o teste rode o script:

-- Criação
create table #Servicos (servico int, codigo int)
create table #itensorcamento (grupo int, codigoref int)
create table #orcamentos (numero int)
-- Inserção de dados
insert into #Servicos (servico, codigo) values (1,100)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #orcamentos (numero) values (500)
insert into #itensorcamento (grupo, codigoref) values (100,600)
insert into #itensorcamento (grupo, codigoref) values (100,700)
insert into #orcamentos (numero) values (600)
insert into #orcamentos (numero) values (700)

-- select que retorna a quantidade de serviços
select s.servico, s.codigo
from #servicos s, #itensorcamento i
where s.codigo = i.grupo and i.codigoref = (select top 1 it.codigoref from #orcamentos orc,
#itensorcamento it where orc.numero = it.codigoref and it.grupo=s.codigo)
order by servico

-- select que retorna a quantidade de serviços, proporcional aos itens
select s.servico, s.codigo
from #servicos s, #itensorcamento i
where s.codigo = i.grupo and i.codigoref in (select it.codigoref from #orcamentos orc,
#itensorcamento it where orc.numero = it.codigoref and it.grupo=s.codigo)
order by servico

Fulvio, eu rodei os dois scripts. Na verdade esta contando como se fosse os subitens e não o serviço em si. eu preciso que ele conte para cada orçamento o serviço e não os subitens dele.

select count (s.servico), s.codigo

from servicos s, itensorcamento i

where

s.codigo = i.grupo

and i.codigoref = (select top 1 it.codigoref

from orcamentos orc,

itensorcamento it

where orc.numero = it.codigoref

and it.grupo=s.codigo

AND CAST(FLOOR(CAST(DATA AS float)) AS datetime) BETWEEN '2010-03-04' AND '2010-03-04')

group by s.servico, s.codigo

order by servico

codigo

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

10 1

(1 row(s) affected)

neste dia especifico entrou so um primeiro emplacamento que possui 10 subitens na itensorcamento referenciando o grupo 1 (serviços) entendeu? preciso que ele conte apenas o numero de serviços, porque na verdade so tem um orçamento com um primeiro emplacamento, com 10 subitens do primeiro emplacamento.

teria que estar fazendo assim:

select (s.servico), s.codigo

from servicos s, itensorcamento i

where

s.codigo = i.grupo

and i.codigoref = (select top 1 it.codigoref

from orcamentos orc,

itensorcamento it

where orc.numero = it.codigoref

and it.grupo=s.codigo

AND CAST(FLOOR(CAST(DATA AS float)) AS datetime) BETWEEN '2010-03-04' AND '2010-03-04')

group by s.servico, s.codigo,i.codigoref

order by servico

servico codigo

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

PRIMEIRO EMPLACAMENTO 1

so que se eu coloco o count no serviço ele me retorna 10 ao invés de 1.

[fulvio]

Boa tarde Micheli,

Bem acho que entendi... rs.

Criei as temporárias e alterei os dados de inserção. Caso o relacionamento não seja este, poste um exemplo por favor.

Fiz o select de acordo com os dados das tabelas temporárias. Faça as adaptações e olhe se é isto que deseja.

-- Criação
create table #Servicos (servico varchar(20), codigo int)
create table #itensorcamento (grupo int, codigoref int)
create table #orcamentos (numero int)

-- Inserção de dados
insert into #Servicos (servico, codigo) values ('Emplacamento',100)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #itensorcamento (grupo, codigoref) values (100,500)
insert into #orcamentos (numero) values (500)

select s.servico, s.codigo
from #servicos s where s.codigo in (select it.grupo 
from #itensorcamento it, #orcamentos o where o.numero = it.codigoref and it.grupo=s.codigo)

[MICHELI_MARTINS]

não fulvio, ainda não deu.

com esse select ele conta apenas um primeiro emplacamento e uma renovação licença entre os dias 3 e 4.

nesses dois dias foram uma renovaçao licença e 3 primeiros emplacamentos.

select count(s.servico), s.codigo

from servicos s

where

s.codigo in (select it.grupo

from itensorcamento it, orcamentos o

where

o.numero = it.codigoref

and it.grupo=s.codigo

AND CAST(FLOOR(CAST(DATA AS float)) AS datetime) BETWEEN '2010-03-03' AND '2010-03-04')

group by s.servico, s.codigo

order by servico

codigo

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

1 1

1 9

preciso que apareça assim:

codigo

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

3 1

1 9

o que melhor se assemelha ao que eu preciso é esse select aqui:

SELECT S.SERVICO, S.CODIGO

FROM ITENSORCAMENTO I, ORCAMENTOS OS, SERVICOS S

WHERE

(I.CODIGOREF) = (OS.NUMERO)

AND S.CODIGO = I.GRUPO

AND CAST(FLOOR(CAST(DATA AS float)) AS datetime) BETWEEN '2010-03-03' AND '2010-03-04'

GROUP BY I.CODIGOREF,S.SERVICO,S.CODIGO

SERVICO CODIGO

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

RENOVAÇÃO DA LICENÇA 9

PRIMEIRO EMPLACAMENTO 1

PRIMEIRO EMPLACAMENTO 1

PRIMEIRO EMPLACAMENTO 1

entre esses dias são 4 orcamentos, so que preciso que ele coloque desse jeito:

SERVICO CODIGO total

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

RENOVAÇÃO DA LICENÇA 9 1

PRIMEIRO EMPLACAMENTO 1 3

[fulvio]

Como não tenho uma base com dados de exemplo, fica mais dificil de testar os selects... mas aos poucos vamos chegando... rs.

Pelo que vi, o contador deve ser no resultado. Não gostaria de utilizar o UNION, mas dê uma olhada se assim funciona. Tive que utilizar o UNION por causa da coluna "s.codigo". Por este motivo, aparecerão NULL´s no codigo onde for realizado a soma. Caso você retire a coluna "s.codigo", não será necessário o UNION.

Retornando este resultado, fica bem tranquilo de tratar as informações... :.)

select s.servico, s.codigo, NULL SOMA
from servicos s where s.codigo in (select it.grupo 
from itensorcamento it, orcamentos o where o.numero = it.codigoref and it.grupo=s.codigo)
UNION
select s.servico, NULL, count(s.servico) SOMA
from servicos s where s.codigo in (select it.grupo 
from itensorcamento it, orcamentos o where o.numero = it.codigoref and it.grupo=s.codigo)
group by s.servico