Click here to Skip to main content
15,886,799 members
Please Sign up or sign in to vote.
1.83/5 (3 votes)
See more:
Hi,
I wanna make [LA,LI,LS,LU,LX,LY] = LUFT as well as [XY,MN,ST] = BASE
Please see below for more understanding
That's my query:

SQL
SELECT 
[ABC] = 'LUFT',
ROUND(SUM([GWU VS]),1) AS [GWU LUFT],
[ABC] = 'BASE',
ROUND(SUM([GWU VS]),1) AS [GWU BASE]
FROM tbl1
WHERE [ABC]='LA' OR [ABC]='LI' OR [ABC]='LS' 
OR [ABC]='LU' OR [ABC]='LX' OR [ABC]='LY' 
AND
[ABC]='XY' OR [ABC]='MN' OR [ABC]='ST'
--GROUP BY [ABC]
--ORDER BY [ABC] ASC 


My Table:
ABC    GWU VS
LA      100
LI      80
LS      50
LU      200
LX      220
LY      150
XY      450
MN      600
ST      85

But I'm getting the wrong results
What i really wanna archieve / expected result:
ABC      GWU VS
LUFT      800
BASE      1135


Is it accomplishable?
Posted
Updated 24-Mar-15 2:44am
v5
Comments
Stephen Hewison 24-Mar-15 5:46am    
I see you want to aggregate to a single value. But can you explain how you get an ABC value of LUFT?

SELECT ABC = 'LUFT', ROUND(SUM([GWU Vorschau]),1) AS [GWU VS] FROM Per201408BS WHERE [ABC]='LA' OR [ABC]='LI' OR [ABC]='LS' OR [ABC]='LU' OR [ABC]='LX' OR [ABC]='LY'

Will give you your result. But that assumes a static value for the ABC column
mikybrain1 24-Mar-15 6:15am    
The ABC is a column name. The ABC Value of LUFT is my intention. I just wanna force these (collections) values [LA,LI,LS,LU,LX,LY] to be renamed LUFT so that instead of getting the my results, i did rather wanna get the expected value.

I hope my explaination is clear :)
mikybrain1 24-Mar-15 8:18am    
Hi Stephen,
I've updated my question. Can u please take a look whether u can help?
mikybrain1 24-Mar-15 6:20am    
I just tried ur suggestion but it just renamed ABC values to LUFT
ABC GWU VS
LUFT 100
LUFT 80
LUFT 50
LUFT 200
LUFT 220
LUFT 150
Instead of aggregating to a sing value
ABC GWU VS
LUFT 800
Stephen Hewison 24-Mar-15 6:24am    
That's because you didn't remove the group by. My query doesn't have the group by clause.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900