February 7, 2002 at 8:05 am
Hello
I have a problem with my Sql Statement and i need to know if is it possible to make a group by clause that returns all records grouping by 'Armazem' and 'article'
I send my statment and there result :
select left(sl.ref,4) as article,(select case when ARMAZEM=1 THEN 'FEIRA' ELSE CASE WHEN ARMAZEM=2 THEN 'PINHEL' ELSE CASE WHEN ARMAZEM=3 THEN 'GUIMAR' ELSE CASE WHEN ARMAZEM=4 THEN 'POVOA' ELSE CASE WHEN ARMAZEM =5 THEN 'VISEU' ELSE CASE WHEN ARMAZEM=99 THEN 'ARM99' END END END END END END) AS ARMAZEM, SUM(QTT) AS QUANT,SUM(QTT*ST.EPCPOND) AS TOTAL,(SELECT CASE WHEN LEFT(SL.REF,4)LIKE '0%' THEN 'ARTIGO' ELSE CASE WHEN LEFT(SL.REF,4) LIKE '200%' THEN ' SEMELLES' ELSE CASE WHEN LEFT(SL.REF,4)NOT LIKE '0%' OR LEFT(SL.REF,4)NOT LIKE '200
%' THEN 'GRUPOS' END END END) AS REFER FROM SL INNER JOIN ST ON ST.REF=SL.REF WHERE ((CM>74 AND CM<77)AND (DATALC>=#1# AND DATALC <=#2#)) GROUP BY ARMAZEM,SL.REF
the result is :
article armazem quant total type
0003 pinhel 1 12.78 Artigo
0003 pinhel 1 12.78 Artigo
0003 pinhel 1 12.90 Artigo
0003 pinhel 1 12.85 Artigo
0003 pinhel 1 12.78 Artigo
0003 pinhel 1 12.78 Artigo
0003 pinhel 1 12.78 Artigo
I need only one line like this :
article armazem quant total type
0003 pinhel 7 89.65 Artigo
it's not possible an group by clause specify an alias or a select statment, and i don´t no how i can perform this operation
Thanks
I hope you can give an useful help
Best regards
Luis Santos
February 7, 2002 at 10:09 am
You can use your select statement as a derived table.
select
article
, armazem
, quant
, sum(total)
, type
from ( insert your sql here)
group by
article
, armazem
, quant
, type
Steve Jones
February 7, 2002 at 10:38 am
Hello again !
Thanks Steve,i will test your suggestion
See you
Best regards
Luis Santos
February 7, 2002 at 4:07 pm
hello again Steve
i test your example but all the time i have the same erro
line 11 :incorrect sintaxe near 'article'
i don´t know if i forget ')' or it´s a problem with alias names.
I send you my code
select armazem
,quant
,sum(total)
,article
from (select (select case when ARMAZEM=1 THEN 'FEIRA' ELSE CASE WHEN ARMAZEM=2 THEN 'PINHEL' ELSE CASE WHEN ARMAZEM=3 THEN 'GUIMAR' ELSE CASE WHEN ARMAZEM=4 THEN 'POVOA' ELSE CASE WHEN ARMAZEM =5 THEN 'VISEU' ELSE CASE WHEN ARMAZEM=99 THEN 'ARM99' END END END END END END) as armazem,
SUM(QTT) as quant,
SUM(QTT*ST.EPCPOND) as total,
(select CASE WHEN LEFT(SL.REF,4)LIKE '0%' THEN 'ARTIGO' ELSE CASE WHEN LEFT(SL.REF,4) LIKE '200%' THEN ' SEMELLES' ELSE CASE WHEN LEFT(SL.REF,4) NOT LIKE '0%' OR LEFT(SL.REF,4) NOT LIKE '200%' THEN 'GRUPOS' END END end) as article
FROM SL INNER JOIN ST ON ST.REF=SL.REF
WHERE ((CM>74 AND CM<77) AND (DATALC>=#1# AND DATALC <=#2#))
group by armazem,quant,article
I hope you can give me the solution
Thanks
Luis Santos
February 7, 2002 at 4:39 pm
Luis,
Try this on for size:
select
aramazem,
article,
sum(quant),
sum(total)
from
(select
case when ARMAZEM=1 THEN 'FEIRA'
WHEN ARMAZEM=2 THEN 'PINHEL'
WHEN ARMAZEM=3 THEN 'GUIMAR'
WHEN ARMAZEM=4 THEN 'POVOA'
WHEN ARMAZEM =5 THEN 'VISEU'
WHEN ARMAZEM=99 THEN 'ARM99'
ELSE '?????'
END as armazem,
CASE
WHEN LEFT(SL.REF,4) LIKE '0%' THEN 'ARTIGO'
WHEN LEFT(SL.REF,4) LIKE '200%' THEN ' SEMELLES'
ELSE 'GRUPOS'
END as article,
SUM(QTT) as quant,
SUM(QTT*ST.EPCPOND) as total
FROM
SL
INNER
JOIN ST ON ST.REF=SL.REF
WHERE
((CM>74 AND CM<77) AND
(DATALC>=#1# AND DATALC <=#2#))
group by
armazem, left(SL.REF, 4)
) as DerrivedTable
group by
aramazem, article
February 7, 2002 at 5:01 pm
Hello Deuce
i´am very grateful they work very well.
if when i use this kind of subquery and i need to make a group by statement by aliases i must use always 'derrivedtable'?
cuold you give me your feedback answer or a simple explanation ?
thanks again for your useful help
Best regards
Luis Santos
February 7, 2002 at 5:07 pm
luissantos I did check and came up with 1 ')' short. However for performance I don't think you need
WHEN LEFT(SL.REF,4) LIKE '0%' THEN 'ARTIGO'
WHEN LEFT(SL.REF,4) LIKE '200%' THEN ' SEMELLES'
try
WHEN SL.REF LIKE '0%' THEN 'ARTIGO'
WHEN SL.REF LIKE '200%' THEN 'SEMELLES'
this should produce the same results but takes a bit of work off the server as you are comparing from the first character thru being set either 4+anything or 200+anything.
February 7, 2002 at 5:17 pm
First off, please get in the habit of formatting your code in such a way that it's easier for you to see what is going on. Start small and make sure that bit of code works before moving on to the next part of the query.
DerrivedTable is just the name I gave it. It could have been XYZ. I used DerrivedTable because that's what this type of subquery is called. SQL will run this query first, and treat the result set like it would a table.
Notice that in the inner query, I grouped by left(SL.REF, 4) which is what you're selecting to make article. You can't group by article in the inside query because the server doesn't recognize it as a field name. You can group by it in the outside query becuase, to sql, it is a field in your "derrived table".
Hope this helps.
John
February 8, 2002 at 4:07 pm
Hello
i´am very grateful for your friendly helps, my T-SQL works very well.
i´d like to now if it is possible with an select statement build a crosstab query like in access , because i will need soon to make a report based on this type of query.
Thanks again
Luis Santos
February 8, 2002 at 4:16 pm
Yes basic format
SELECT
MAINVALUE,
SUM(CASE WHEN PIVOTCOL = VAL1 THEN 1 ELSE 0 END) AS VAL1,
SUM(CASE WHEN PIVOTCOL = VAL2 THEN 1 ELSE 0 END) AS VAL2,
.....AND SO ON
FROM TABLEX
February 8, 2002 at 4:16 pm
Sorry forgot
GROUP BY MAINVALUE
February 8, 2002 at 4:30 pm
obrigado (that's mean thank you in portuguese)
good weekend
Luis Santos
February 8, 2002 at 4:37 pm
DA NADA (Means Your welcome in spanish).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply