UNION Query with ORDER BY SUM

union query access
sql union
union vs union all
access union query make table
vertica union all
db2 union example
how to combine two sql queries in one result
sql combine two queries with different columns

Here is what I would like to do:

SELECT 
  TOP 25 tblTasks.Task, 
  tblTasks.Task_Status, 
  tblTasks.Award_Date, 
  tblTasks.End_Date, 
  Sum(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, 
  tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
FROM 
  (tblTasks INNER JOIN tblClient ON tblTasks.Task_ID = tblClient.Task_ID) 
   INNER JOIN tblPRs ON tblTasks.Task_ID = tblPRs.Task_ID
GROUP BY 
   tblTasks.Task, 
   tblTasks.Task_Status, 
   tblTasks.Award_Date, 
   tblTasks.End_Date, 
   tblTasks.Program, 
   tblTasks.Prime, tblClient.Org_Top
HAVING 
   (((tblTasks.Task_Status)="open"))

UNION

SELECT 
   TOP 25 tblTasks.Task, 
   tblTasks.Task_Status, 
   tblTasks.Award_Date, 
   tblTasks.End_Date, Sum(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, 
   tblTasks.Program, 
   tblTasks.Prime, tblClient.Org_Top
FROM 
   (tblTasks INNER JOIN tblClient ON tblTasks.Task_ID = tblClient.Task_ID) 
    INNER JOIN tblPRs ON tblTasks.Task_ID = tblPRs.Task_ID
GROUP BY 
    tblTasks.Task, 
    tblTasks.Task_Status, 
    tblTasks.Award_Date, 
    tblTasks.End_Date, 
    tblTasks.Program, 
    tblTasks.Prime, 
    tblClient.Org_Top
HAVING 
    (((tblTasks.Task_Status)="pending"))

ORDER BY 
    Sum(tblPRs.Ceiling_Final) DESC;

I am entering the SQL into MS Access. The only difference between the two queries is the HAVING parameter value. The individual queries came from MS Access Query Design. Each individual query when paired with the ORDER BY statement works fine and gives me the results I expect. When I try to run them both as a UNION I get the following error:

The ORDER BY expression (Sum(tblPRs.Ceiling_Final)) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

nb: I have assumed you want just the 25 rows with highest SumOfCeiling_Final

I suggest you should to form a "derived table" first, using union all, then seek the top n rows. Please note that the having clause is NOT a substitute for the where clause. A having clause is necessary IF filtering conditions need aggregated values (e.g. having sum(cost) > 1000 ). If the filtering conditions don't involve aggregates place those in the where clause.

SELECT TOP 25
    *
FROM (
    SELECT
        tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, SUM(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
    FROM (tblTasks
    INNER JOIN tblClient
        ON tblTasks.Task_ID = tblClient.Task_ID)
    INNER JOIN tblPRs
        ON tblTasks.Task_ID = tblPRs.Task_ID
    WHERE tblTasks.Task_Status = "open"
    GROUP BY
        tblTasks.Task
       ,tblTasks.Task_Status
       ,tblTasks.Award_Date
       ,tblTasks.End_Date
       ,tblTasks.Program
       ,tblTasks.Prime
       ,tblClient.Org_Top

    UNION ALL

    SELECT
        tblTasks.Task, tblTasks.Task_Status, tblTasks.Award_Date, tblTasks.End_Date, SUM(tblPRs.Ceiling_Final) AS SumOfCeiling_Final, tblTasks.Program, tblTasks.Prime, tblClient.Org_Top
    FROM (tblTasks
    INNER JOIN tblClient
        ON tblTasks.Task_ID = tblClient.Task_ID)
    INNER JOIN tblPRs
        ON tblTasks.Task_ID = tblPRs.Task_ID
    WHERE tblTasks.Task_Status = "pending"
    GROUP BY
        tblTasks.Task
       ,tblTasks.Task_Status
       ,tblTasks.Award_Date
       ,tblTasks.End_Date
       ,tblTasks.Program
       ,tblTasks.Prime
       ,tblClient.Org_Top
    ) d
ORDER BY
    SumOfCeiling_Final DESC
;

NB UNION ALL does NOT remove duplicates rows, UNION does remove duplicate rows. So, UNION ALL actually does LESS work i.e. it is faster. In addition, in the upper query every rows must contain Task_Status = "open" and the lower they must contain Task_Status = "pending" so the 2 queries produce rows that must be different. Although without data to verify I can't be certain, it seems you only need to use union all.

UNION Clause, Each SELECT statement in a UNION clause can specify its own ORDER BY , LIMIT , and OFFSET UNION combines the aggregate results from each query:. The ORDER BY expression (Sum(tblPRs.Ceiling_Final)) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression. sql database sql-order-by union access

I believe one of the following will accomplish what it looks like you are after:

SELECT *
FROM (put your 1st query here
      UNION
      put your 2nd query here)
ORDER BY SumOfCeiling_Final DESC;

-or-

SELECT * FROM (put your 1st query here, and include ORDER BY Sum(tblPRs.Ceiling_Final) DESC)
UNION
SELECT * FROM (put your 2nd query here, and include ORDER BY Sum(tblPRs.Ceiling_Final) DESC)

My sql-fu is not strong enough to know for sure that these would work, without fiddling around with actual data; speaking of which, could you set up a SQL Fiddle with the minimal schema and data to illustrate your question / problem?

EDIT: For the record, I also like the answer @Used_By_Already posted, and obviously his/her answer is very much like my first alternative; use the one that works the best for your needs.

Summarizing query results using aggregate functions, HAVING clause, and ORDER BY clause to group and sort the results of queries using aggregate functions, and the UNION operator to combine the results of  It is not possible to use two different ORDER BY in the UNION statement. UNION returns single resultsetand as per the Logical Query Processing Phases . However, if your requirement is such that you want your top and bottom query of the UNION resultset independently sorted but in the same resultset you can add an additional static column and

I ended up putting each query into a derived table, and then performing the UNION on the derived tables.

SELECT *
FROM ( placed first query here including the ORDER BY
) AS a

UNION ALL

SELECT *
FROM ( placed second query here including the ORDER BY
) AS b;

Using the UNION keyword to combine subselects, Using the UNION keyword, you can combine two or more subselects to form a fullselect. When SQL The query returns the following interim result table. A name may be specified on the ORDER BY clause if the result columns are named. Fact 1: ORDER BY follows the last subselect and applies to the entire union. A common mistake that people make is to put an ORDER BY clause on each subselect. Doing so is not allowed because it is unnecessary. The system combines the result sets from all the subselects, then sorts the combined data.

Use a union query to combine multiple queries into a single result , The last part of this SQL statement determines the ordering of the combined records by using an ORDER BY statement. In this example, Access will order all of the  SQL Union overview, usage and examples September 25, 2018 by Prashanth Jayaram This article will provide a deep dive into the SQL Union operator, describing its many uses along with examples and explore some common questions like the differences between Union vs Union All.

SQL: UNION ALL Operator, The SQL UNION ALL operator is used to combine the result sets of 2 or more UNION ALL operator with different column names and order the query results. I am trying to sum two queries to produce a single record. Instead it is giving me the results of both queries. The results are correct, I just need them to be combined. Below is my query. The end result is a customers balance based on whatever date is used as the variable.

UNION, UNION queries cannot be used with aggregate functions. ALL/DISTINCT. The ALL keyword causes duplicate rows to be  DEFINE. EVALUATE. ORDER BY. RETURN. VAR. ORDER BYDAX Statement. The ORDER BY keyword is part of the EVALUATE statement and defines the sort order of the result produced by EVALUATE. It can include only columns that are part of the result of EVALUATE.

Comments
  • are you hoping to get 25 rows from each of the 2 queries (50 rows in total)? or do you just want the top 25?
  • 25 from each for a total of 50. I may actually change the second query to 10 for a total of 35.
  • Thanks to the moderator for cleaning up my post.
  • I am pretty sure I tried to work around the problem previously with a derived table. Seems to me I get an error stating there is an issue with the FROM syntax. I will try the solution at work in the AM. Also,I need the top 25 from each query. How would that change what you did? The HAVING clause was created by MS Design Query - I don't know why. I thought it should be a WHERE also.
  • Found a stale copy of the DB at home. I tried your solution and it appears to work. I also moved the SELECT TOP 25 into the two original queries (to give me 50 rows). I have a problem with the stale DB so I cannot tell for sure if I am getting the data exactly the way I want it. The good news is that it RUNS and I think I am on the right track now. I am not sure why my prior attempt using the same method as yours failed - I had a syntax issue somewhere. THANK YOU!!
  • So moving the TOP 25 from the outside to the each of the inside queries give me random results (query runs but the data is not what I expect). How can we modify your solution to give TOP 25 for each of the inside queries?
  • The solution appears to be put each SELECT TOP query (including ORDER BY) into its own derived table then UNION ALL the derived tables. I have this working on a minimal schema. I'll post the final solution (if it works) once I apply to my active DB.
  • I am a real noob at this. No clue how to set up a Fiddle. If you explain it I could try. Also, as in my prior comment on prior answer, I am pretty sure I tried to get this solution to work but get an error related to FROM syntax. Will try tomorrow.
  • @John 1. go to SQL Fiddle 2. on the left, enter schema statements (CREATE TABLE, etc.) and data population statements (INSERT, etc) then click "Build Schema" 3. on the right, enter a query and click "Run SQL" 4. tinker away
  • @John note that you can select an RDBMS; I don't think you are attempting anything that is too specific to a particular RDBMS, so any of them should do ... I believe