Sql group by, selected fields not equal to group by fields

I have this query and works well, but i need another field in  "from  (select entidade,moeda,sinal," line, but did not want to add more fields to group by " group by entidade,moeda,sinal ". Is possible

select MovDet.*, Cli.nome, Cli.morada, Cli.localidade,cli.CodigoPostal,cli.Telefone,
CONVERT(varchar, '1900-01-01', 102) AS Datadocumento, '' as numdocumento, 'Saldo Anterior' as Descritivo,
0.00 as saldoanterior,  '' as empresanome, '' as titulomapa, '' as criteriomapa, MovDet.entidade as Idstamp
from  (select entidade,moeda,sinal,
case
      when Sinal= 'C' and moeda= 'EURO' Then sum(Debito)
      when Sinal= 'C' and moeda <> 'EURO' Then sum(Debito/cotacao)
      else 0
end as debito,

case
      when Sinal= 'D' and moeda= 'EURO' Then sum(Credito)
      when Sinal= 'D' and moeda <> 'EURO' Then sum(Credito/cotacao)
      else 0
end as credito
from DetDocCCclientes
where
DetDocCCclientes.DataDocumento <'2011-03-17'
and
DetDocCCclientes.entidade >=200
and  
DetDocCCclientes.entidade <=200 group by entidade,moeda,sinal ) MovDet
left join clientes Cli  on Cli.CodigoCliente = MovDet.entidade
rflorencioAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
does this provide what you desire?

it would be easier to assist if you could tell us what it is that you are trying to display
and why you need to avoid adding into the group by clause...

some sample data/expected result would also assist....

an inital "standard" method would be to just add max(idstamp) as idstamp
to the select list....

did you read the article angeliii pointed you too?
select MovDet.*, Cli.nome, Cli.morada, Cli.localidade,cli.CodigoPostal,cli.Telefone,
CONVERT(varchar, '1900-01-01', 102) AS Datadocumento, '' as numdocumento, 'Saldo Anterior' as Descritivo,
0.00 as saldoanterior,  '' as empresanome, '' as titulomapa, '' as criteriomapa, MovDet.entidade as Idstamp
from  (select distinct entidade,moeda,sinal,idstamp
,sum(case sinal when 'C' then debito / (case moeda when 'EURO' then 1.000000 else cotacao end) else 0 end) 
 over (partition by entidade,moeda,sinal order by entidade) as debito
,sum(case sinal when 'D' then credito / (case moeda when 'EURO' then 1.000000 else cotacao end) else 0 end) 
 over (partition by entidade,moeda,sinal order by entidade) as credito

from DetDocCCclientes
where DetDocCCclientes.DataDocumento <'2011-03-17'
and DetDocCCclientes.entidade >=200
and  DetDocCCclientes.entidade <=200 
left join clientes Cli 
 on Cli.CodigoCliente = MovDet.entidade

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
it depends ....
read this article to understand the issue and find the sql solutions:
http://www.experts-exchange.com/A_3203.html
0
 
LowfatspreadCommented:
as angeliii says it depends...

what do you want to also display?

you current query may be better written as

select MovDet.*, Cli.nome, Cli.morada, Cli.localidade,cli.CodigoPostal,cli.Telefone,
CONVERT(varchar, '1900-01-01', 102) AS Datadocumento, '' as numdocumento, 'Saldo Anterior' as Descritivo,
0.00 as saldoanterior,  '' as empresanome, '' as titulomapa, '' as criteriomapa, MovDet.entidade as Idstamp
from  (select entidade,moeda,sinal,
sum(case sinal when 'C' then debito / (case moeda when 'EURO' then 1.000000 else cotacao end) else 0 end) as debito
sum(case sinal when 'D' then credito / (case moeda when 'EURO' then 1.000000 else cotacao end) else 0 end) as credito

from DetDocCCclientes
where DetDocCCclientes.DataDocumento <'2011-03-17'
and DetDocCCclientes.entidade >=200
and  DetDocCCclientes.entidade <=200
group by entidade,moeda,sinal ) MovDet
left join clientes Cli
 on Cli.CodigoCliente = MovDet.entidade
0
 
rflorencioAuthor Commented:
in this line i need add another field (idstamp), but in this case i need add to gropu bt this field also, and i do not want.

from  (select entidade,moeda,sinal,idstamp
case
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.