asked on

SQL QUERY

I need assistance with the following SQL Query. The Leads Total and Tech's totals are working producing a product as desired; however, the total available is not summing.

SELECT        ID, Dates, Sta, Leads, Leads * 4 AS LeadsTotal, Techs, Techs * 8.5 AS TechsTotal, PTO, TRG, NonR, Rout, 
                         LeadsTotal + TechsTotal - NonR - Rout AS TotalAvail
FROM            PAF1Sql

Open in new window

Dale Burrell

You can't make use of a computed column at the same level of the query.

SELECT ID, Dates, Sta, Leads, LeadsTotal, Techs, TechsTotal, PTO, TRG, NonR, Rout
  , LeadsTotal + TechsTotal - NonR - Rout AS TotalAvail
from
(
  SELECT ID, Dates, Sta, Leads
    , Leads * 4 AS LeadsTotal
    , Techs, Techs * 8.5 AS TechsTotal, PTO, TRG, NonR, Rout
  FROM
) x

Open in new window

ASKER CERTIFIED SOLUTION

QuinnDex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Burrell

QuinnDex's way will also work, but more scope for error, i.e. you have to keep the same calculation identical in multiple locations. I would suggest therefore that that isn't best practice for writing queries.

BOEING39

ASKER

I am getting a syntax error.

SELECT ID, Dates, Sta, Leads, LeadsTotal, Techs, TechsTotal, PTO, TRG, NonR, Rout
, LeadsTotal + TechsTotal - NonR - Rout AS TotalAvail
FROM PAF1Sql;

(
SELECT ID, Dates, Sta, Leads
, Leads * 4 AS LeadsTotal
, Techs, Techs * 8.5 AS TechsTotal, PTO, TRG, NonR, Rout
FROM PAF1Sql;

) x

Dale Burrell

Well if you tell us what the error is... Or even if you're feeling brave, google it ;)

You haven't the query I posted, you're using something inbetween, from both tables. Try the one I posted

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Any chance any of those values are NULL? If so, NULL + anything = NULL.

Chris Luttrell

In your posted query, you are doing 2 queries not the subquery like suggested. Try this

SELECT ID, Dates, Sta, Leads, LeadsTotal, Techs, TechsTotal, PTO, TRG, NonR, Rout
  , LeadsTotal + TechsTotal - NonR - Rout AS TotalAvail
FROM
(
  SELECT ID, Dates, Sta, Leads
    , Leads * 4 AS LeadsTotal
    , Techs, Techs * 8.5 AS TechsTotal, PTO, TRG, NonR, Rout
  FROM PAF1Sql
) x

Open in new window

BOEING39

ASKER

Everyone thanks for the quick responses. I utilized QuinnDex response to get it to work; however, As Paul pointed out if the multipliers are variable this would not work so well so I did finally utilize parameters. Again thanks for those who responded.