Здравствуйте, Alex.Che, Вы писали:

>> А как правильно?

AC>так правильно.
AC>но это не весь текст.

Разумеется не весь. Весь запрос ниже; в последнем поле запроса хотелось бы написать total=income-expense-vat

SELECT DISTINCT
 goods.id,
 goods.product_code, 
 goods.name,
 SUM(waybill_items.quantity) AS quantity,
 SUM(waybill_items.price * COALESCE(cur_rates.rate, 1.0) * waybill_items.quantity - waybill_items.discount) AS income,
 SUM(waybill_items.vat * COALESCE(cur_rates.rate, 1.0)) AS vat,

 CASE WHEN 0={fifo-or-lifo} THEN
  SUM(waybill_items.buy_price * waybill_items.quantity)
 ELSE
  (SELECT SUM(_cost) FROM
  (
  SELECT
   shipment_sales.buy_price * shipment_sales.quantity * COALESCE(currency_rates.rate, 1.0) AS _cost
  FROM
   shipment_sales,
   waybill_items AS wi,
   waybills AS wb LEFT OUTER JOIN currency_rates ON currency_rates.start_date=
            (SELECT MAX(start_date) 
             FROM currency_rates 
             WHERE start_date<=wb.waybill_date AND 
                   currency_rates.currency_id=wb.currency_id
            )
  WHERE
   shipment_sales.is_production_sell=0 AND
   shipment_sales.sell_item_id=waybill_items.id AND
   shipment_sales.is_production_buy=0 AND
   shipment_sales.buy_item_id=wi.id AND
   wi.waybill_id=wb.id

  UNION ALL

  SELECT
   shipment_sales.buy_price * shipment_sales.quantity * COALESCE(currency_rates.rate, 1.0) AS _cost
  FROM
   shipment_sales,
   production LEFT OUTER JOIN currency_rates ON currency_rates.start_date=
            (SELECT MAX(start_date) 
             FROM currency_rates 
             WHERE start_date<=production.production_date AND 
                   currency_rates.currency_id=production.buy_currency_id
            )
  WHERE
   shipment_sales.is_production_sell=0 AND
   shipment_sales.sell_item_id=waybill_items.id AND
   shipment_sales.is_production_buy=1 AND
   shipment_sales.buy_item_id=production.id
  )
 )
 END AS expense,
 25.0 AS total

FROM
 goods,
 waybills,
 waybill_items LEFT OUTER JOIN currency_rates AS cur_rates
               ON cur_rates.currency_id=waybills.currency_id AND
               cur_rates.start_date=
                  (
                   SELECT MAX(start_date) 
                   FROM currency_rates 
                   WHERE start_date<=waybills.waybill_date AND 
                         currency_rates.currency_id=waybills.currency_id
                  )

WHERE
 goods.group_id=? AND
 goods.is_deleted=0 AND
 goods.id=waybill_items.goods_id AND
 waybill_items.is_deleted=0 AND
 waybill_items.waybill_id=waybills.id AND
 waybills.is_deleted=0 AND
 waybills.record_type=-1 AND
 waybills.waybill_date>={date-begin} AND
 waybills.waybill_date<={date-end} AND
 waybills.shop_id IN ({shop-ids}) AND
 waybills.is_reserve=0
GROUP BY
 product_code, name
ORDER BY
 product_code, name

Здравствуйте, Alex.Che, Вы писали:

>> А как правильно?

AC>так правильно.
AC>но это не весь текст.

Разумеется не весь. Весь запрос ниже; при исполнении запроса получаю ошибку Column unknown: income. Если последнее поле запроса убрать, то запрос выполняется без ошибок.

SELECT DISTINCT
 goods.id,
 goods.product_code, 
 goods.name,
 SUM(waybill_items.quantity) AS quantity,
 SUM(waybill_items.price * COALESCE(cur_rates.rate, 1.0) * waybill_items.quantity - waybill_items.discount) AS income,
 SUM(waybill_items.vat * COALESCE(cur_rates.rate, 1.0)) AS vat,

 CASE WHEN 0={fifo-or-lifo} THEN
  SUM(waybill_items.buy_price * waybill_items.quantity)
 ELSE
  (SELECT SUM(_cost) FROM
  (
  SELECT
   shipment_sales.buy_price * shipment_sales.quantity * COALESCE(currency_rates.rate, 1.0) AS _cost
  FROM
   shipment_sales,
   waybill_items AS wi,
   waybills AS wb LEFT OUTER JOIN currency_rates ON currency_rates.start_date=
            (SELECT MAX(start_date) 
             FROM currency_rates 
             WHERE start_date<=wb.waybill_date AND 
                   currency_rates.currency_id=wb.currency_id
            )
  WHERE
   shipment_sales.is_production_sell=0 AND
   shipment_sales.sell_item_id=waybill_items.id AND
   shipment_sales.is_production_buy=0 AND
   shipment_sales.buy_item_id=wi.id AND
   wi.waybill_id=wb.id

  UNION ALL

  SELECT
   shipment_sales.buy_price * shipment_sales.quantity * COALESCE(currency_rates.rate, 1.0) AS _cost
  FROM
   shipment_sales,
   production LEFT OUTER JOIN currency_rates ON currency_rates.start_date=
            (SELECT MAX(start_date) 
             FROM currency_rates 
             WHERE start_date<=production.production_date AND 
                   currency_rates.currency_id=production.buy_currency_id
            )
  WHERE
   shipment_sales.is_production_sell=0 AND
   shipment_sales.sell_item_id=waybill_items.id AND
   shipment_sales.is_production_buy=1 AND
   shipment_sales.buy_item_id=production.id
  )
 )
 END AS expense,
 income - expense - vat AS total

FROM
 goods,
 waybills,
 waybill_items LEFT OUTER JOIN currency_rates AS cur_rates
               ON cur_rates.currency_id=waybills.currency_id AND
               cur_rates.start_date=
                  (
                   SELECT MAX(start_date) 
                   FROM currency_rates 
                   WHERE start_date<=waybills.waybill_date AND 
                         currency_rates.currency_id=waybills.currency_id
                  )

WHERE
 goods.group_id=? AND
 goods.is_deleted=0 AND
 goods.id=waybill_items.goods_id AND
 waybill_items.is_deleted=0 AND
 waybill_items.waybill_id=waybills.id AND
 waybills.is_deleted=0 AND
 waybills.record_type=-1 AND
 waybills.waybill_date>={date-begin} AND
 waybills.waybill_date<={date-end} AND
 waybills.shop_id IN ({shop-ids}) AND
 waybills.is_reserve=0
GROUP BY
 product_code, name
ORDER BY
 product_code, name