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 :

Amount = Mean Value x Quantity x Ratio

while when the unit of measurement is a ‘KGR’ then the formula becomes:

Amount = Mean Value x Quantity / Ratio

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:

SELECT
 r.material_id, m.unit,
 CASE
  WHEN m.unit = 'PIECES'
   THEN m.mean_value * r.quantity * m.ratio
   //formula 1
 ELSE IF m.unit = 'KGR'
   THEN m.mean_value * r.quantity / m.ratio
  // formula 2
 END as amount
 from requests r, materials m
  where r.year = '1/1/13' and
        r.req_id = 1 and
        r.material_id = m.material_id

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 :

SELECT
 m.material_id,
 m.unit,
 m.mean_value * r.quantity * m.ratio as amount, 
               a.account_category,
               a.account_id
 from requests r, materials m, accounts a
 where r.year = '1/1/13' and
       r.material_id = m.material_id and
       r.material_id = a.material_id and
       r.year = m.year and
       r.year = a.year and
       m.unit = 'PIECES'
 UNION ALL
SELECT
 m.material_id,
 m.unit,
 m.mean_value * r.quantity / m.ratio as  
   amount,
   a.account_category,
   a.account_id
from requests r, materials m, accounts a
where r.year = '1/1/13' and
      r.material_id = m.material_id and
      r.material_id = a.material_id and
      r.year = m.year and
      r.year = a.year and
      m.unit = 'KGR'

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: 

CASE
 WHEN m.unit = 'PIECES'
  THEN m.mean_value * r.quantity * m.ratio
 ELSE IF m.unit = 'KGR'
  THEN m.mean_value * r.quantity / m.ratio
 ELSE IF m.unit = 'LT'
  THEN
 ELSE IF m.unit=….
  THEN ….
END as amount


to:

CASE
 WHEN m.unit = 'PIECES'
  THEN r.mean_value * r.quantity * m.ratio
 ELSE r.mean_value * r.quantity / m.ratio
END as amount

Our first attempt to get to the sums would be: 

SELECT
 a.account_category, a.account_id,
CASE
 WHEN m.unit = 'PIECES'
  THEN sum(m.mean_value * r.quantity * m.ratio)
  ELSE sum(m.mean_value * r.quantity / m.ratio)
END as amount
from requests r, materials m, accounts a
where r.year = '1/1/13' and
      r.material_id = m.material_id and
      r.material_id = a.material_id and
      r.year = m.year and
      r.year = a.year
      GROUP BY account_category, account_id

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
GROUP BY clause.

Dead end?