I am trying to group stock levels into four columns depending on their location
This is difficult to put in words so this bit of pseudo code may explain better - SELECT part, descr,
-
CASE WHEN loc NOT IN ('Y1','Y2','F1','F2','T1','T2',) THEN SUM(stock) AS local,
-
CASE WHEN loc IN ('Y1','Y2',) THEN SUM(stock) AS Yard,
-
CASE WHEN loc IN ('F1''F2') THEN SUM(stock) AS Field,
-
CASE WHEN loc IN ('T1','T2') THEN SUM(stock) AS Transit,
-
I wrote this code then realised it would simply return the same figures every time.
Can any suggest how to do this better?
That doesn't seem very like the code I suggested. For the record, I would certainly not expect that to work, with or without [loc] in the GROUP BY clause. Let me take exactly what you've posted and put my version in then you can copy/paste the suggested code in and test it : - SELECT RTRIM(LTRIM(part)) part, descr,
-
SUM(CASE WHEN [loc] NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN [stock] ELSE 0 END) AS local,
-
SUM(CASE WHEN [loc] IN ('Y1','Y2') THEN [stock] ELSE 0 END) AS Yard,
-
SUM(CASE WHEN [loc] IN ('F1','F2') THEN [stock] ELSE 0 END) AS Field,
-
SUM(CASE WHEN [loc] IN ('T1','T2') THEN [stock] ELSE 0 END) AS Transit
-
FROM [bins]
-
GROUP BY [part], [descr]
-
ORDER BY [part]
9 1830 NeoPa 32,556
Expert Mod 16PB
I would expect the following to work : - SELECT [part]
-
, [descr]
-
, SUM(CASE WHEN [loc] NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN [stock] ELSE 0) AS local
-
, SUM(CASE WHEN [loc] IN ('Y1','Y2') THEN [stock] ELSE 0) AS [Yard]
-
, SUM(CASE WHEN [loc] IN ('F1','F2') THEN [stock] ELSE 0) AS [Field]
-
, SUM(CASE WHEN [loc] IN ('T1','T2') THEN [stock] ELSE 0) AS [Transit]
Hi NeoPa. I have been playing around with something similar; because an aggregate funtion (SUM) is used then part and loc has to be in a GROUP BY.
As soon as 'GROUP BY loc' is used, a seperate record for each location is created and the SUM(stock) just shows the stock in that location, rather than aggregating the stock into location groups.
NeoPa 32,556
Expert Mod 16PB
My understanding is somewhat different. If an item is used within an aggregate function then it must not be included in the GROUP BY clause. Any item referred to outside of aggregation must be.
As such, I would expect to see [part] and [descr] in the GROUP BY clause but not [loc] or [stock], and certainly no smoking barrels (:D).
What results do you get when you try it?
NeoPa 32,556
Expert Mod 16PB
I did some testing on a table in my database and it seemed to work fine, except I had the syntax a little wrong. I'd missed off the END keyword of each CASE statement. It should read : - SELECT [part]
-
, [descr]
-
, SUM(CASE WHEN [loc] NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN [stock] ELSE 0 END) AS local
-
, SUM(CASE WHEN [loc] IN ('Y1','Y2') THEN [stock] ELSE 0 END) AS [Yard]
-
, SUM(CASE WHEN [loc] IN ('F1','F2') THEN [stock] ELSE 0 END) AS [Field]
-
, SUM(CASE WHEN [loc] IN ('T1','T2') THEN [stock] ELSE 0 END) AS [Transit]
"Column 'bins.loc' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause"
That is using this piece of code - SELECT RTRIM(LTRIM(part)) part, descr,
-
CASE WHEN loc NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN SUM(stock) END AS local,
-
CASE WHEN loc IN ('Y1','Y2') THEN SUM(stock) END AS Yard,
-
CASE WHEN loc IN ('F1''F2') THEN SUM(stock) AS Field,
-
CASE WHEN loc IN ('T1','T2') THEN SUM(stock) AS Transit,
-
FROM bins
-
GROUP BY part, descr, --loc
-
ORDER BY part
If I un-edit 'loc', then the correct stock is aligned under the correct heading, but there is an entry for each location. - part local
-
N0026 1515.0000
-
N0026 31.0000
-
Because the two figures are in seperate locations they are not aggregated
NeoPa 32,556
Expert Mod 16PB
That doesn't seem very like the code I suggested. For the record, I would certainly not expect that to work, with or without [loc] in the GROUP BY clause. Let me take exactly what you've posted and put my version in then you can copy/paste the suggested code in and test it : - SELECT RTRIM(LTRIM(part)) part, descr,
-
SUM(CASE WHEN [loc] NOT IN ('Y1','Y2','F1','F2','T1','T2') THEN [stock] ELSE 0 END) AS local,
-
SUM(CASE WHEN [loc] IN ('Y1','Y2') THEN [stock] ELSE 0 END) AS Yard,
-
SUM(CASE WHEN [loc] IN ('F1','F2') THEN [stock] ELSE 0 END) AS Field,
-
SUM(CASE WHEN [loc] IN ('T1','T2') THEN [stock] ELSE 0 END) AS Transit
-
FROM [bins]
-
GROUP BY [part], [descr]
-
ORDER BY [part]
That doesn't seem very like the code I suggested
We posted minutes apart so I missed your suggestion. I was answering
What results do you get when you try it
CASE inside the SUM. I have never used that, so will give it a try
Yes that does the trick.
Good thinking NeoPa
NeoPa 32,556
Expert Mod 16PB
Thanks CG. I'm pleased that worked for you.
BTW. I'm going to switch the Best Answer to post #7 as the earlier one (#2) was a bit faulty. It missed off the END of each CASE WHEN.
I'd rather anyone searching for a solution found the one that can work properly.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andrew |
last post by:
With command-line interface ( 3.23.37, UNIX Socket ) all is well with
column aliasing. However, column aliases disappear in Excel, over
ODBC, when there are multiple (joined) tables in the query.
...
|
by: Kevin |
last post by:
I'm making my first attempt to put embedded
SQL in a visual C++ application and I'm
having trouble getting aliases to work.
If I try the following SQL query on MS SQL 7.0
it works fine.
SELECT...
|
by: Krzysiek |
last post by:
Hi all,
I have an issue with QSYS\QADBXREF file - it keeps aliases on tables.
I take care of an application that works on many places (servers) and
on one of them it's not possible to create...
|
by: John Bailo |
last post by:
I have created several aliases to members using the CREATE ALIAS
command, but I lost track of them.
Now I want to see a catalog of all the aliases mapped to all members in
a FILE.
Is there a...
|
by: Nick White [MSFT] |
last post by:
Hello all:
I'd like to take a moment to inform you of the existence of new product
group-monitored feedback email aliases we've instituted for Windows
Embedded. This feedback medium allows you...
|
by: mp |
last post by:
i have a python program which attempts to call 'cls' but fails:
sh: line 1: cls: command not found
i tried creating an alias from cls to clear in .profile, .cshrc, and
/etc/profile, but none...
|
by: jacob navia |
last post by:
Recently, we had a very heated thread about GC with the usual
arguments (for, cons, etc) being exchanged.
In one of those threads, we came into the realloc problem.
What is the realloc...
|
by: Daniel Rucareanu |
last post by:
I have the following script:
function Test(){}
Test.F = function(){}
Test.F.FF = function(){}
Test.F.FF.FFF = function(){}
Test.F.FF.FFF.FFFF = function(){}
//var alias = function(){};
var...
|
by: Martin Douglas |
last post by:
Using VS2005, Win32 projects expose on the properties of a reference
the "Aliases" property, which defaults to "global" for a value. Here
one can define in comma-delimitted format a list of...
|
by: dom.k.black |
last post by:
Is it still common practice to use type aliases (INT, PCHAR etc).
It looks ugly and breaks the syntax highlighting, are there any
advantages these days (MSVC++6.0 and later)? I could understand...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |