Solved

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

Posted on 2011-03-17
4
543 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:rflorencio
  • 2
4 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
Comment Utility
it depends ....
read this article to understand the issue and find the sql solutions:
http://www.experts-exchange.com/A_3203.html
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
 

Author Comment

by:rflorencio
Comment Utility
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 400 total points
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now