Solved

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

Posted on 2011-03-17
4
566 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 35157257
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
ID: 35157440
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
ID: 35157866
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
ID: 35158225
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql case statement to calculate totals 5 53
How come this XML node is not read? 3 51
Database-Scoped Permissions 2 40
Get data from two MySQL tables 6 62
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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