|SQL Workshop - Selecting columns without a non-aggregate column|
|Written by Nikos Vaggalis|
|Thursday, 19 December 2013|
Page 1 of 2
Thinking in terms of sets and set operations can be difficult at first but after a while you discover that you can do things without needed to drop down to procedural approaches.
This scenario requires us to be members of a hospital's Dietary Department and with the end of the year approaching we are assigned the task of estimating the amount of money needed for next year’s resource shopping, to keep the patients fed for the coming twelve months.
So we need to find the sum of the mean amount spent on the resources/raw material (vegetables, fruit, meat etc) grouped by Account Category (i.e. the account that serves for fruit) and Account Id (actual account number) used for their shopping, and use that as the basis for our new season’s budget estimate.
When a request for, say, fruit comes in, we translate that request into the amount of money consumed using a formula based on the fruit’s dynamically updated Mean Value, the Quantity of the request and a Ratio.
When the unit of measurement is 'PIECES' then our formula is :
while when the unit of measurement is a ‘KGR’ then the formula becomes:
The nature and meaning of the two formulae isn't as important as the fact that they vary according to the value of the Unit field.
It is this variation of the formulas that will create the most trouble, as we’ll soon find out.
Let’s get a preview of our data together with an attempt to implement the formulas:
The desired result set would be :
If we would also require their Account Category and Account Id (where the money for their purchasing is deducted from), the above query would become :
Producing a result set like :
Of course our result set would include more units of measurement than PIECES and KGR and in a moment we will generalize in order to include additional values.
Using procedural logic we could now write a little program in Perl or any programming language which would treat the result set as a collection of values, iterate through it, and add to a variable called $AmountPieces when encountering a unit of PIECES and $AmountKGR when encountering a unit of KGR, doing our calculations row by row.
But let’s choose another path, that of pure SQL and set based logic.
In any case, since we are exclusively interested in the monetary aggregates, we can forgo the attributes of Material_id and Unit in the final answer as long as we get the formulas right.
Let's say that for reasons of simplicity, only PIECES are calculated differently and the rest of the units are all calculated using the same formula, something that simplifies our query from something like:
Our first attempt to get to the sums would be:
but instead of the results we are expecting we are confronted with the following error message instead :
E_US0B63 line 1, The columns in the SELECT clause must be contained in the
|Last Updated ( Thursday, 06 September 2018 )|