[maluktuk]

I'm trying my hand at using variables, and unfortunately I think I'm missing a key principle somewhere. The following query worked before I tried to declare, set, and then use variables to calculate a margin percentage. Is there a glaring error in this that, once corrected, will make this work as desired?

Thanks in advance for the help.

DECLARE @i1 decimal(10,2)
DECLARE @i2 decimal(10,2)

SET @i1 = ct.ordamt
SET @i2 = (ct.ordamt - ct.costamt)

SELECT COUNT (ct.ordno) AS "# of Orders"
     , SUM (ct.ordamt) AS "Total"
     , SUM (ct.ordamt - ct.costamt) AS "Margin"
     , @i2 / @i1 AS "Margin %"
   FROM ordrhist AS ct
WHERE
ct.orddate = curdate() - 0 AND SUBSTRING(ct.status,1,1)<>'C'AND SUBSTRING(ct.status,2,1)='O' AND ct.hold <> 'Y' AND ct.complete <> 'I'

[swampBoogie]

I assume that you expect that when using

, @i2 / @i1

it should be substituted by

ct.ordamt/(ct.ordamt - ct.costamt)

when executing the select statement. Variables does not work that way. Variables can only hold values, e.g. strings and integer, not a column reference. I think that attempting to use variables that way, if that is your intention, only makes the code less readable.

[maluktuk]

Originally Posted by swampBoogie

I assume that you expect that when using

, @i2 / @i1

it should be substituted by

ct.ordamt/(ct.ordamt - ct.costamt)

when executing the select statement. Variables does not work that way. Variables can only hold values, e.g. strings and integer, not a column reference. I think that attempting to use variables that way, if that is your intention, only makes the code less readable.

SwampBoogie,

You assumed correctly. Thanks for letting me know about variables -- I'm definitely new to this and am grateful to learn everything I can. I had originally tried something like that before but I kept getting an error. This is what I was trying:

SELECT COUNT (ct.ordno) AS "# of Orders"
     , SUM (ct.ordamt) AS "Total"
     , SUM (ct.ordamt - ct.costamt) AS "Margin"
     , (ct.ordamt - ct.costamt) / ct.ordamt AS "Margin %"
   FROM ordrhist AS ct
WHERE
ct.orddate = curdate() - 0 AND SUBSTRING(ct.status,1,1)<>'C'AND SUBSTRING(ct.status,2,1)='O' AND ct.hold <> 'Y' AND ct.complete <> 'I'

If I leave out the third SELECT statement, everything works fine. It's when I try to calculate a margin percentage that I get an error. Any thoughts?

[r937]

the problem is, when you assign a column alias to an expression (like your two SUMs), you are not allowed to reference those column aliases in the same SELECT clause

two workarounds --

1. repeat the expressions

SELECT COUNT(*) AS "# of Orders"
     , SUM(ct.ordamt) AS "Total"
     , SUM(ct.ordamt - ct.costamt) AS "Margin"
     , ( SUM(ct.ordamt) -
         SUM(ct.ordamt - ct.costamt) ) / 
         SUM(ct.ordamt) AS "Margin %"
  FROM ordrhist AS ct
 WHERE ct.orddate = curdate() - 0 
   AND SUBSTRING(ct.status,1,1)<>'C'
   AND SUBSTRING(ct.status,2,1)='O' 
   AND ct.hold <> 'Y' 
   AND ct.complete <> 'I'

2. push the query down into a subquery as a derived table

SELECT "# of Orders"
     , "Total"
     , "Margin"
     , ( "Total" - "Margin" ) / "Total" AS "Margin %"
  FROM ( SELECT COUNT(*) AS "# of Orders"
              , SUM(ct.ordamt) AS "Total"
              , SUM(ct.ordamt - ct.costamt) AS "Margin"
           FROM ordrhist AS ct
          WHERE ct.orddate = curdate() - 0 
            AND SUBSTRING(ct.status,1,1)<>'C'
            AND SUBSTRING(ct.status,2,1)='O' 
            AND ct.hold <> 'Y' 
            AND ct.complete <> 'I' ) AS dt

[maluktuk]

Originally Posted by r937

the problem is, when you assign a column alias to an expression (like your two SUMs), you are not allowed to reference those column aliases in the same SELECT clause

two workarounds --

1. repeat the expressions

SELECT COUNT(*) AS "# of Orders"
     , SUM(ct.ordamt) AS "Total"
     , SUM(ct.ordamt - ct.costamt) AS "Margin"
     , ( SUM(ct.ordamt) -
         SUM(ct.ordamt - ct.costamt) ) / 
         SUM(ct.ordamt) AS "Margin %"
  FROM ordrhist AS ct
 WHERE ct.orddate = curdate() - 0 
   AND SUBSTRING(ct.status,1,1)<>'C'
   AND SUBSTRING(ct.status,2,1)='O' 
   AND ct.hold <> 'Y' 
   AND ct.complete <> 'I'

2. push the query down into a subquery as a derived table

SELECT "# of Orders"
     , "Total"
     , "Margin"
     , ( "Total" - "Margin" ) / "Total" AS "Margin %"
  FROM ( SELECT COUNT(*) AS "# of Orders"
              , SUM(ct.ordamt) AS "Total"
              , SUM(ct.ordamt - ct.costamt) AS "Margin"
           FROM ordrhist AS ct
          WHERE ct.orddate = curdate() - 0 
            AND SUBSTRING(ct.status,1,1)<>'C'
            AND SUBSTRING(ct.status,2,1)='O' 
            AND ct.hold <> 'Y' 
            AND ct.complete <> 'I' ) AS dt

r937,

Thanks again for the help. The first one returned an error (I suspect an issue with the program I'm using), but the second one worked like a charm! That's actually what I had in mind in my original request but did such a poor job of verbalizing. I'm hoping to pick up more of this SQL lingo.

Is there a simple way to add a thousand separator to the "Total" and "Margin" amounts and limit the "Margin %" to two decimal points?

PS - I tried finding your book through the link in your signature? Is that still available, and would it be a good resource for a newb like me?

[r937]

Originally Posted by maluktuk

Is there a simple way to add a thousand separator to the "Total" and "Margin" amounts and limit the "Margin %" to two decimal points?

FORMAT() -- if you're on SQL Server 2012

otherwise, it's better to do this in the front-end application

Originally Posted by maluktuk

PS - I tried finding your book through the link in your signature? Is that still available, and would it be a good resource for a newb like me?

yes it's still available, and yes, you might benefit from it

the reason there's no link is too long a story to go into now

you can get it from amazon or from http://www.sitepoint.com/books/sql1/