Say I have two tables:

KnownHours:
&lt;pre&gt;
ChargeNum    CategoryID    Month    Hours
111111       1             2/1/09   10
111111       1             3/1/09   30
111111       1             4/1/09   50
222222       1             3/1/09   40
111111       2             4/1/09   50
&lt;/pre&gt;

UnknownHours:
&lt;pre&gt;
ChargeNum   Month   Hours
111111      2/1/09  70
111111      3/1/09  40.5
222222      7/1/09  25.5
&lt;/pre&gt;

I need to group these hours, ignoring Month, into a single data table so that my expected result is the following:

&lt;pre&gt;
ChargeNum    CategoryID     Hours
111111       1              90
111111       2              50
111111       Unknown        110.5
222222       1              40
222222       Unknown        25.5
&lt;/pre&gt;

I cannot seem to figure this out. Any help would be greatly appreciated!

EDIT: I need to sum the hours for each ChargeNum/Category combination.  I updated the sample data to reflect this.

Combine two tables for one output

What is the difference between `JOIN` and `UNION`? Can I have an example?



What is the difference between JOIN and UNION?

I have a SQL query, looks something like this:

    select name, count (*) from Results group by name order by name

and another, identical which loads from a archive results table, but the fields are the same.

    select name, count (*) from Archive_Results group by name order by name

How would I combine the two in just one query? (So the group by would still function correctly). I tried with union all, however it won&#39;t work. What am I missing?

Using union and count(*) together in SQL query

I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn&#39;t work.

Query1: Works

    SELECT * FROM TABLE1
    UNION
    SELECT * FROM TABLE2 

Query2: Does not Work. 
Error: `Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near &#39;)&#39;.`

    SELECT * INTO [NEW_TABLE]
    FROM
    (
    SELECT * FROM TABLE1
    UNION
    SELECT * FROM TABLE2
    )

Thanks!


SELECT INTO USING UNION QUERY

I have a stored procedure I need to call several different times passing in different paramaters each time. I would like to collect the results as a single dataset. Is something like this possible ...

    exec MyStoredProcedure 1
    UNION
    exec MyStoredProcedure 2
    UNION
    exec MyStoredProcedure 3

  I tried using the syntax above but got the error ...

    Incorrect syntax near the keyword &#39;UNION&#39;

  The stored procedures I am dealing with are pretty complex and sort of a &quot;black box&quot; to me, so I cannot get into the definition of the stored procedure and change anything. Any suggestions on how to gather the results together?

  I am using SQL Server 2008 R2. Thanks for any help.



UNION the results of multiple stored procedures

I am performing multiple iterations of the type:

    masterSet=masterSet.union(setA)

As the set grows the length of time taken to perform these operations is growing (as one would expect, I guess).

I expect that the time is taken up checking whether each element of setA is already in masterSet?

My question is that if i KNOW that masterSet does not already contain any of elements in setA can I do this quicker? 

[UPDATE]

Given that this question is still attracting views I thought I would clear up a few of the things from the comments and answers below: 

When iterating though there were many iterations where I **knew** `setA` would be distinct from `masterSet` because of how it was constructed (without having to process any checks) but a few iterations I needed the uniqueness check.

I wondered if there was a way to &#39;tell&#39; the `masterSet.union()` procedure not to bother with the uniquness check this time around as I know this one is distinct from `masterSet` just add these elements quickly trusting the programmer&#39;s assertion they were definately distict. Perhpas through calling some different &quot;`.unionWithDistinctSet()`&quot; procedure or something. 

I think the responses have suggested that this isnt possible (and that really set operations should be quick enough anyway) but to use `masterSet.update(setA)` instead of union as its slightly quicker still. 

I have accepted the clearest reponse along those lines, resolved the issue I was having at the time and got on with my life but would still love to hear if my hypothesised `.unionWithDistinctSet()` could ever exist?

Quick way to extend a set if we know elements are unique

Is it possible to order when the data is come from many select and union it together? Such as

    Select id,name,age
    From Student
    Where age &lt; 15
    Union
    Select id,name,age
    From Student
    Where Name like &quot;%a%&quot;

How can I order this query by name?

I tried this

    Select id,name,age
    From Student
    Where age &lt; 15 or name like &quot;%a%&quot;
    Order by name

But that does not work. 






How to order by with union in SQL?

I want to create different methods for a class called [Multiset][1].

I have all the required methods, but I&#39;m unsure of how to write intersection, union, and subset methods.

For intersection and union, my code starts like this:

    def intersect(var)
      x = Multiset.new
    end

Here is an example:

    X = [1, 1, 2, 4]
    Y = [1, 2, 2, 2]

then the intersection of `X` and `Y` is `[1, 2]`. 

[1]: http://en.wikipedia.org/wiki/Multiset

How can I get the intersection, union, and subset of arrays in Ruby?

Are there any methods to do so? I was looking but couldn&#39;t find any.

Another question: I need these methods so I can filter files. 
Some are `AND` filters and some are `OR` filters (like in set theory), so I need to filter according to all files and the unite/intersects ArrayLists that holds those files.

Should I use a different data structure to hold the files? Is there anything else that would offer a better runtime?


Intersection and union of ArrayLists in Java

I want to use order by with union in mysql query.
I am fetching different types of record based on different criteria from a table based on distance for a search on my site.
The first select query returns data related to the exact place search .
The 2nd select query returns data related to distance within 5 kms from the place searched.
The 3rd select query returns data related to distance within 5-15 kms from the place searched.

Then i m using union to merge all results and show on a page with paging. Under appropriate heading as **&#39;Exact search results&#39;**, **&#39;Results within 5 kms&#39;** etc

Now i want to sort results based on id or add_date. But when i add order by clause at the end of my query ( query1 union query 2 union query 3 order by add_date). It sorts all results. But what i want is it should sort under each heading.

Using union and order by clause in mysql

I have a recursive function that creates objects representing file paths (the keys are paths and the values are info about the file). It&#39;s recursive as it&#39;s only meant to handle files, so if a directory is encountered, the function is recursively called on the directory.

All that being said, I&#39;d like to do the equivalent of a set union on two maps (i.e. the &quot;main&quot; map updated with the values from the recursive call). Is there an idiomatic way to do this aside from iterating over one map and assigning each key, value in it to the same thing in the other map?

That is: given `a,b` are of type `map [string] *SomeObject`, and `a` and `b` are eventually populated, is there any way to update `a` with all the values in `b`?

Getting the union of two maps in go

I&#39;ve written a couple of complex queries (at least to me) with Ruby on Rail&#39;s query interface:

    
    watched_news_posts = Post.joins(:news =&gt; :watched).where(:watched =&gt; {:user_id =&gt; id})
    watched_topic_posts = Post.joins(:post_topic_relationships =&gt; {:topic =&gt; :watched}).where(:watched =&gt; {:user_id =&gt; id})

Both of these queries work fine by themselves.  Both return Post objects.  I would like to combine these posts into a single ActiveRelation.  Since there could be hundreds of thousands of posts at some point, this needs to be done at the database level.  If it were a MySQL query, I could simply user the `UNION` operator.  Does anybody know if I can do something similar with RoR&#39;s query interface?

ActiveRecord Query Union

I have a **Jobs** and a **Companies** table, and I want to extract 20 jobs that meet the following criteria: 

1. Jobs only from two (2) named companies  
2. There can at most be 10 jobs per company

I have tried the following `SELECT` with `UNION DISTINCT`, but the problem is that the `LIMIT 0,10` applies to the whole result set. I want it to apply to each of the companies. 

If there aren&#39;t 10 jobs per company, then the query should return all the jobs it finds.

    SELECT c.name, j.title, j.`desc`, j.link 
      FROM jobs_job j
    INNER JOIN companies_company c ON j.company_id = c.id
    WHERE c.name IN (&#39;Company1&#39;)
    UNION DISTINCT
    SELECT c.name, j.title, j.`desc`, j.link 
      FROM jobs_job j
    INNER JOIN companies_company c ON j.company_id = c.id
    WHERE c.name IN (&#39;Company2&#39;)
    ORDER by name, title
    LIMIT 0,10

I am new to MySQL, so realise there may be a smarter way to do this instead of with UNION, so any suggestions for improvements are definitely welcome.

Combining UNION and LIMIT operations in MySQL query

Given two polygons:

    POLYGON((1 0, 1 8, 6 4, 1 0))
    POLYGON((4 1, 3 5, 4 9, 9 5, 4 1),(4 5, 5 7, 6 7, 4 4, 4 5))

How can I calculate the union (combined polygon)?

[![enter image description here][1]][1]

[Dave&#39;s example][2] uses SQL server to produce the union, but I need to accomplish the same in code. I&#39;m looking for a mathematical formula or code example in any language that exposes the actual math. I am attempting to produce maps that combine countries dynamically into regions. I asked a related question here: https://stackoverflow.com/questions/2653812/grouping-geographical-shapes


  [1]: https://i.stack.imgur.com/N5P2R.png
  [2]: http://blogs.msdn.com/davidlean/archive/2008/10/25/sql-2008-spatial-samples-part-n-2-on-n-geometric-set-theory-methods.aspx

How do I combine complex polygons?

How can one programmatically sort a union query when pulling data from two tables? For example,

    SELECT table1.field1 FROM table1 ORDER BY table1.field1
    UNION
    SELECT table2.field1 FROM table2 ORDER BY table2.field1

Throws an exception

Note: this is being attempted on MS Access Jet database engine



SQL Query - Using Order By in UNION

What is the easiest way to compare the elements of two lists say A and B with one another, and add the elements which are present in B to A only if they are not present in A?

To illustrate,
Take list A = {1,2,3}
list B = {3,4,5}

So after the operation AUB I want
list A = {1,2,3,4,5}

Simplest way to form a union of two lists

I just read part of an optimization article and *segfaulted* on the following statement:

&gt; When using SQL replace statements using `OR` with a `UNION`:

&gt;     select username from users where company = ‘bbc’ or company = ‘itv’;

&gt; to:
 
&gt;     select username from users where company = ‘bbc’ union
    select username from users where company = ‘itv’;

From a quick `EXPLAIN`:

Using `OR`:

![enter image description here][1]

Using `UNION`:

![enter image description here][2]

Doesn&#39;t this mean `UNION` does in *double the work*?

While I appreciate `UNION` may be more performant for certain RDBMSes and certain table schemas, this is not *categorically true* as the author suggestions.

### Question
Am I wrong?


  [1]: http://i.stack.imgur.com/sxoLS.png
  [2]: http://i.stack.imgur.com/HAqu6.png

SQL Performance UNION vs OR

how can i use **union** and **order by** in mysql ?

    select * from _member_facebook 
    inner join _member_pts 
    ON _member_facebook._fb_owner=_member_pts._username 
    where _member_facebook._promote_point = 9 
    ORDER BY RAND() limit 2 
    UNION ALL
    select * from _member_facebook 
    inner join _member_pts 
    ON _member_facebook._fb_owner=_member_pts._username 
    where _member_facebook._promote_point = 8 limit 3

give me error 

    #1221 - Incorrect usage of UNION and ORDER BY

any one can help ?

Incorrect usage of UNION and ORDER BY?

I want to learn how to combine two db tables which have no fields in common. I&#39;ve checked UNION but MSDN says :

&gt; The following are basic rules for combining the result sets of two queries by using UNION:
&gt;
&gt; 1.  The number and the order of the columns must be the same in all queries.
&gt; 2.  The data types must be compatible.

But I have no fields in common at all. All I want is to combine them in one table like a view.

So what should I do?


Combine two tables that have no common fields

What is the difference between `UNION` and `UNION ALL`?



What is the difference between UNION and UNION ALL?

How do you use `UNION` with multiple `Common Table Expressions`?

I&#39;m trying to put together some summary numbers but no matter where I put the `;`, I always get an error 

    SELECT  COUNT(*)
    FROM    dbo.Decision_Data
    UNION
    SELECT  COUNT(DISTINCT Client_No)
    FROM    dbo.Decision_Data
    UNION
    WITH    [Clients]
              AS ( SELECT   Client_No
                   FROM     dbo.Decision_Data
                   GROUP BY Client_No
                   HAVING   COUNT(*) = 1
                 )
        SELECT  COUNT(*) AS [Clients Single Record CTE]
        FROM    Clients;

I appreciate in the example above I can move the **single** CTE to the beginning, but I have a number of CTEs I&#39;d like to `UNION`

How do you UNION with multiple CTEs?

How to use union all in LINQ TO SQL.
I have use the following code for union, then how to use this for union all?

    List&lt;tbEmployee&gt; lstTbEmployee = obj.tbEmployees.ToList();
    List&lt;tbEmployee2&gt; lstTbEmployee2 = (from a in lstTbEmployee
                                        select new tbEmployee2
                                        {
                                            eid = a.eid,
                                            ename = a.ename,
                                            age = a.age,
                                            dept = a.dept,
                                            doj = a.doj,
                                            dor = a.dor
    
                                        }).Union(obj.tbEmployee2s).ToList();

How to use union all in LINQ?

What alternatives do I have to implement a union query using hibernate? I know hibernate does not support union queries at the moment, right now the only way I see to make a union is to use a view table.

The other option is to use plain jdbc, but this way I would loose all my example/criteria queries goodies, as well as the hibernate mapping validation that hibernate performs against the tables/columns.

Hibernate Union alternatives

	(SELECT COUNT(motorbike.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type=&#39;motobike&#39;
	AND owner.`owner_id`=motorbike.`owner_id`
	AND transport.`type_id`=motorbike.`motorbike_id` GROUP BY motorbike.owner_id)
	UNION ALL
	(SELECT COUNT(car.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type=&#39;car&#39;
	AND owner.`owner_id`=car.`owner_id`
	AND transport.`type_id`=car.`car_id` GROUP BY car.`owner_id`)


The query above returns a result like this below,

    count          name
    1              Linda
    2              Mary
    1              Steve
    1              Linda

This query is to count how many transport that owned by an owner. Linda have one car and one motorcycle,so the result should:

    count          name
    2              Linda
    2              Mary
    1              Steve

I have tried this query,but return error:


	(SELECT COUNT(motorbike.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type=&#39;motobike&#39;
	AND owner.`owner_id`=motorbike.`owner_id`
	AND transport.`type_id`=motorbike.`motorbike_id`)
	UNION ALL
	(SELECT COUNT(car.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type=&#39;car&#39;
	AND owner.`owner_id`=car.`owner_id`
	AND transport.`type_id`=car.`car_id`)  GROUP BY motorbike.owner_id

Can anyone help me please?


Group by with union mysql select query

I&#39;m doing a `UNION` of two queries on an Oracle database. Both of them have a `WHERE` clause. Is there a difference in the performance if I do the `WHERE` after `UNION`ing the queries compared to performing the `UNION` after `WHERE` clause?

For example:

    SELECT colA, colB FROM tableA WHERE colA &gt; 1
    UNION
    SELECT colA, colB FROM tableB WHERE colA &gt; 1

compared to:

    SELECT * 
      FROM (SELECT colA, colB FROM tableA
            UNION
            SELECT colA, colB FROM tableB) 
     WHERE colA &gt; 1

I believe in the second case, it performs a full table scan on both the tables affecting the performance. Is that correct?

All Union Solutions on Gang of Coders

Total of 25 Union Solutions