x

how to get the difference between two aliases

this is my query and its working.

 SELECT  description,
         C.date,
         SUM(C.quantity) AS quantity,
         (C.quantity)
         + (SELECT   COALESCE(SUM(item_count.quantity), 0)
            FROM     item_count
                     JOIN supplier
                     ON supplier.supplier_id = item_count.supplier_id
            WHERE    sup_name = '4PS'
                     AND MONTH(item_count.date) < MONTH(now())
           ) AS total,
         SUM(D.quantity) AS TP
 FROM    item AS A
         JOIN supplier AS B
         ON B.supplier_id = A.supplier_id
         JOIN item_count AS C
         ON C.item_id = A.item_id
         JOIN [transaction] AS D
         ON D.item_id = A.item_id
 WHERE   sup_name = '4PS'
         AND MONTH(C.date) = MONTH(now())
         AND MONTH(d.trans_date) = MONTH(now())
         AND company = '4PS'
 ORDER BY description;

I want to add a column which selects the difference of total and TP which are aliases.

please help. tnx.

more ▼

asked Feb 12, 2014 at 02:23 PM in Default

avatar image

CrookS50
1 1 1 2

On a side note, those calculations against your columns are going to absolutely kill performance: MONTH(C.Date), etc. I would suggest adding a column to the table that can store the calculated value and even index on it.

Feb 12, 2014 at 02:47 PM Grant Fritchey ♦♦

The site name is SQL Server Central. Most people on here are SQL Server people. If you don't specify that you're using something other than SQL Server, you're likely to get answers for SQL Server, which may be problematic in a MySQL system.

Feb 13, 2014 at 07:27 PM Grant Fritchey ♦♦

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.

Feb 16, 2014 at 10:52 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

There's no way to do that directly without rerunning the calculations.

So, a few options:

  1. Rerun the calculations to build the column you want

  2. Use a CTE - Common Table Expression - defined as per your query, and select the values from it with the extra column (probably the simplest to write)

  3. Create a VIEW based on your query, and SELECT from that

  4. Store the data from your SELECT statement into a temporary table (or a table variable), and use that.

  5. Do the final calculation at the presentation layer - your application, report, whatever.

Edit

So, the CTE option:

 ;
 WITH    MyCTE
           AS ( SELECT   description ,
                         C.date ,
                         SUM(C.quantity) AS quantity ,
                         ( C.quantity )
                         + ( SELECT  COALESCE(SUM(item_count.quantity), 0)
                             FROM    item_count
                                     JOIN supplier ON supplier.supplier_id = item_count.supplier_id
                             WHERE   sup_name = '4PS'
                                     AND MONTH(item_count.date) < MONTH(now())
                           ) AS total ,
                         SUM(D.quantity) AS TP
                FROM     item AS A
                         JOIN supplier AS B ON B.supplier_id = A.supplier_id
                         JOIN item_count AS C ON C.item_id = A.item_id
                         JOIN [transaction] AS D ON D.item_id = A.item_id
                WHERE    sup_name = '4PS'
                         AND MONTH(C.date) = MONTH(now())
                         AND MONTH(d.trans_date) = MONTH(now())
                         AND company = '4PS'
              )
     SELECT  Description ,
             Date ,
             Quantity ,
             Total ,
             TP ,
             Total - TP AS NewTotal
     FROM    MyCTE
     ORDER BY Description

Note that you can't have an ORDER BY clause in a CTE - after all, you don't have one in a TABLE, or in a VIEW, right?

Second EDIT - given you now say you're using MySQL rather than SQL Server, CTE's aren't available. I would suggest a further subquery, along these lines:

 SELECT  Description ,
         Date ,
         Quantity ,
         Total ,
         TP ,
         Total - TP AS NewTotal
 FROM    ( SELECT    description ,
                     C.date ,
                     SUM(C.quantity) AS quantity ,
                     ( C.quantity )
                     + ( SELECT  COALESCE(SUM(item_count.quantity), 0)
                         FROM    item_count
                                 JOIN supplier ON supplier.supplier_id = item_count.supplier_id
                         WHERE   sup_name = '4PS'
                                 AND MONTH(item_count.date) < MONTH(now())
                       ) AS total ,
                     SUM(D.quantity) AS TP
           FROM      item AS A
                     JOIN supplier AS B ON B.supplier_id = A.supplier_id
                     JOIN item_count AS C ON C.item_id = A.item_id
                     JOIN [transaction] AS D ON D.item_id = A.item_id
           WHERE     sup_name = '4PS'
                     AND MONTH(C.date) = MONTH(now())
                     AND MONTH(d.trans_date) = MONTH(now())
                     AND company = '4PS'
         )
 ORDER BY Description



more ▼

answered Feb 12, 2014 at 02:41 PM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

where's the part to change... I'm planning to use CTE but I don't know where the part to use it though. a little help pls? ^_^.

Feb 12, 2014 at 02:48 PM CrookS50
(comments are locked)
10|1200 characters needed characters left

You have two options.

One is to repeat the code for TP and Total and subtract the first expression from the later.

The second option is to use a Common Table Expression

An example:

 create table AliasTest(a int, b int, c int);
 
 insert AliasTest(a,b,c)
 values(1,2,3);
 
 WITH CTE AS(
 SELECT a*b as ATIMESB, a+c as APLUSC FROM AliasTest 
 )
 SELECT ATIMESB, APLUSC, ATIMESB - APLUSC as Difference from cte;
more ▼

answered Feb 12, 2014 at 02:35 PM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

OK, @Magnus, you beat me to it. But at least I came up with a couple of other options, that may be even less efficient.

Feb 12, 2014 at 02:42 PM ThomasRushton ♦♦

And this teaches me to never say that there are a finite number of options to solve a problem :)

Feb 13, 2014 at 01:14 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

oh... It makes sense but I can't run it on wamp. is CTE on SQL? sorry noob ^_^

more ▼

answered Feb 12, 2014 at 04:03 PM

avatar image

CrookS50
1 1 1 2

Ah, right. MySQL. That does rule out the CTE option; however, you can convert it relatively straightforwardly into another sub-query. Hang on, I'll update my answer.

Feb 13, 2014 at 07:00 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x162
x126
x4

asked: Feb 12, 2014 at 02:23 PM

Seen: 3174 times

Last Updated: Feb 16, 2014 at 10:52 AM

Copyright 2018 Redgate Software. Privacy Policy