How to do a count on a union query
If you want a total count for all records, then you would do this: SELECT COUNT(*) FROM ( select distinct profile_id from userprofile_… union all select distinct profile_id from productions_… ) x
union
Merge two rows in SQL
Aggregate functions may help you out here. Aggregate functions ignore NULLs (at least that’s true on SQL Server, Oracle, and Jet/Access), so you could use a query like this (tested on SQL Server Express 2008 R2): SELECT FK, MAX(Field1) AS Field1, MAX(Field2) AS Field2 FROM table1 GROUP BY FK; I used MAX, but any aggregate … Read more
Quick way to extend a set if we know elements are unique
You can use set.update to update your master set in place. This saves allocating a new set all the time so it should be a little faster than set.union… >>> s = set(range(3)) >>> s.update(range(4)) >>> s set([0, 1, 2, 3]) Of course, if you’re doing this in a loop: masterSet = set() for setA … Read more
UNION the results of multiple stored procedures
You’d have to use a temp table like this. UNION is for SELECTs, not stored procs CREATE TABLE #foo (bar int …) INSERT #foo exec MyStoredProcedure 1 INSERT #foo exec MyStoredProcedure 2 INSERT #foo exec MyStoredProcedure 3 … And hope the stored procs don’t have INSERT..EXEC.. already which can not be nested. Or multiple resultsets. … Read more
SELECT INTO USING UNION QUERY
You have to define a table alias for a derived table in SQL Server: SELECT x.* INTO [NEW_TABLE] FROM (SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2) x “x” is the table alias in this example.
Combine two tables for one output
You’ll need to use UNION to combine the results of two queries. In your case: SELECT ChargeNum, CategoryID, SUM(Hours) FROM KnownHours GROUP BY ChargeNum, CategoryID UNION ALL SELECT ChargeNum, ‘Unknown’ AS CategoryID, SUM(Hours) FROM UnknownHours GROUP BY ChargeNum Note – If you use UNION ALL as in above, it’s no slower than running the two … Read more
UNION with WHERE clause
In my experience, Oracle is very good at pushing simple predicates around. The following test was made on Oracle 11.2. I’m fairly certain it produces the same execution plan on all releases of 10g as well. (Please people, feel free to leave a comment if you run an earlier version and tried the following) create … Read more
Using union and count(*) together in SQL query
SELECT tem.name, COUNT(*) FROM ( SELECT name FROM results UNION ALL SELECT name FROM archive_results ) AS tem GROUP BY name ORDER BY name
Hibernate Union alternatives
You could use id in (select id from …) or id in (select id from …) e.g. instead of non-working from Person p where p.name=”Joe” union from Person p join p.children c where c.name=”Joe” you could do from Person p where p.id in (select p1.id from Person p1 where p1.name=”Joe”) or p.id in (select p2.id … Read more
Group by with union mysql select query
select sum(qty), name from ( select count(m.owner_id) as qty, o.name from transport t,owner o,motorbike m where t.type=”motobike” and o.owner_id=m.owner_id and t.type_id=m.motorbike_id group by m.owner_id union all select count(c.owner_id) as qty, o.name, from transport t,owner o,car c where t.type=”car” and o.owner_id=c.owner_id and t.type_id=c.car_id group by c.owner_id ) t group by name