Use column defined in a subquery

Question

Sorry if the title is not clear, I'm a beginner and I didn't know exactly how to formule it...

I have this query working with Oracle :

SELECT

   ( SELECT COUNT(*)
     FROM   CATEGORY
   ) AS NBCATEGORIES,

   ( SELECT ROUND(AVG(FINANCIALOPERATIONBYPERSON),2)
    FROM
         (
            SELECT SUM(AMOUNT) AS FINANCIALOPERATIONBYPERSON
            FROM FINANCIALOPERATION
            WHERE PERSONID IS NOT NULL
            GROUP BY PERSONID
         )
     ) AS AVERAGELOADAMOUNTBYPERSON

FROM DUAL

I'm looking for the equivalent for Sql Server...

The goal is to have multiple queries in a single query.

So I removed the "FROM DUAL" but I get an error on "FINANCIALOPERATIONBYPERSON" (Invalid column name), certainly because it's defined in the subquery...

How can I modify the query for SQL-Server ?

Does it work if you alias the subquery? e.g. FROM (SELECT blah from blah) AS MyName — DavidG
– DavidG, Commented Oct 7, 2014 at 14:15

Gordon Linoff · Accepted Answer · 2014-10-07 14:16:02Z

2

SQL Server requires aliases for subqueries. So, you can rewrite this as:

SELECT (SELECT COUNT(*)
        FROM   CATEGORY
       ) AS NBCATEGORIES,
       (SELECT ROUND(AVG(FINANCIALOPERATIONBYPERSON),2)
        FROM (SELECT SUM(AMOUNT) AS FINANCIALOPERATIONBYPERSON
              FROM FINANCIALOPERATION
              WHERE PERSONID IS NOT NULL
              GROUP BY PERSONID
             ) t
       ) AS AVERAGELOADAMOUNTBYPERSON;

In both databases, though, I would be inclined to write this as:

SELECT c.NBCATEGORIES, ROUND(fo.AVERAGELOADAMOUNTBYPERSON, 2) AS AVERAGELOADAMOUNTBYPERSON
FROM (SELECT COUNT(*) as NBCATEGORIES
      FROM CATEGORY c
     ) c CROSS JOIN
     (SELECT SUM(AMOUNT) / COUNT(DISTINCT PERSONID) AS AVERAGELOADAMOUNTBYPERSON
      FROM FINANCIALOPERATION fo
      WHERE PERSONID IS NOT NULL
     ) fo;

One note for both these forms: SQL Server does integer arithmetic on integers. So, if AMOUNT is an integer, then you should convert it to an appropriate floating or fixed point numeric type.

answered Oct 7, 2014 at 14:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3544117 Over a year ago

Thanks! So your second query will work with the two databases ? That's exactly what I was looking for!

Gordon Linoff Over a year ago

The simple answer is to either add it in one of the subqueries in the from or add another subquery. If this doesn't help, then ask another question with relevant information.

Vulcronos · Accepted Answer · 2014-10-07 14:16:12Z

1

You need to add a table alias for the subquery.

SELECT
( SELECT COUNT(*)
     FROM   CATEGORY
   ) AS NBCATEGORIES,

   ( SELECT ROUND(AVG(RESULTS.FINANCIALOPERATIONBYPERSON),2)
    FROM
         (
            SELECT SUM(AMOUNT) AS FINANCIALOPERATIONBYPERSON
            FROM FINANCIALOPERATION
            WHERE PERSONID IS NOT NULL
            GROUP BY PERSONID
         ) RESULTS
     ) AS AVERAGELOADAMOUNTBYPERSON

answered Oct 7, 2014 at 14:16

Vulcronos

3,4563 gold badges19 silver badges25 bronze badges

Collectives™ on Stack Overflow

Use column defined in a subquery

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related