Append Results from Two Queries and Output as a Single Table

Append Results from two queries and output as a single table

select * from products where producttype=magazine
union
select * from products where producttype = book

How do I combine two queries on the same table to get a single result set in MySQL

It is easy actually :)

you can use UNION like this:

SELECT * FROM (
     (SELECT * FROM user WHERE n_id=1 LIMIT 2)
     UNION
     (SELECT * FROM user WHERE n_id=2 LIMIT 2)) 
 collection;

if you read this article about the documentation you can use the () to group the individual queries and the apply the union in the middle. Without the parenthesis it would still LIMIT 2 and show only the two first. Ref. "To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:"

Merge Two SQL Queries to get Output in Single Table

Better way to do this:

SELECT a.agencyid,
       a.name AS Agency,
       a.abbrev,
       aa.no_of_address,
       c.no_of_collaboration
FROM Agency a
LEFT JOIN
  (SELECT agencyid,
          count(agencyid) AS no_of_address
   FROM AgencyAddress
   GROUP BY agencyid) aa ON a.agencyid = aa.agencyid
LEFT JOIN
  (SELECT agencyid,
          count(agencyid) AS no_of_collaboration
   FROM Collaboration
   GROUP BY agencyid) c ON a.agencyid = c.agencyid
ORDER BY a.name;

I think it is more efficient that the other answers.

One more way to achieve this can be:

SELECT a.agencyid,
       a.name AS Agency,
       a.abbrev,
  (SELECT count(aa.agencyid)
   FROM AgencyAddress aa
   WHERE aa.agencyid=a.agencyid) AS no_of_address,
  (SELECT count(c.agencyid)
   FROM Collaboration c
   WHERE c.agencyid=a.agencyid) AS no_of_collaboration
FROM Agency a
ORDER BY a.name;

This is easier to read but not as efficient as the above one.

Combine results of two queries with the same value into one row

Use your query as a sub-query:

with cte as (
    select fldUserId, count(*) as TOTAL
    from tblWorkHistory
    where fldStatus = '1'
    group by fldUserId
    union
    select fldEmpID, count(*)
    from tblQAHistory
    where fldStatus = '1'
    group by fldEmpID
)
select fldUserId, sum(TOTAL) as TOTAL
from cte
group by fldUserId

Combining the results of two queries into one line

If I understood you correctly, this might be one option: split queries into two (temp1 and temp2) and then JOIN them:

WITH
   temp1
   AS
      (SELECT a.RecordID,
              (ACADEMICYEAR (SYSDATE) - c.year_begin + 1) COURSE,
              c.year_begin year_begin,
              c.Name StGroup
         FROM fc_StudentOrders a
              LEFT JOIN fc_OrderTypes b ON b.TypeID = a.OrderType
              LEFT JOIN fc_StudentGroups c ON c.Code = a.StudentGroupID
              LEFT JOIN RB_DEPARTMENTS d ON d.code = c.faculty
        WHERE     a.RecordID = 205838
              AND a.ORDERTYPE IN (15, 56, 109)),
   temp2
   AS
      (SELECT a.RecordID,
              (ACADEMICYEAR (SYSDATE) - c.year_begin + 1) COURSE,
              c.year_begin year_begin,
              c.Name StGroup
         FROM fc_StudentOrders a
              LEFT JOIN fc_OrderTypes b ON b.TypeID = a.OrderType
              LEFT JOIN fc_StudentGroups c ON c.Code = a.StudentGroupID
              LEFT JOIN RB_DEPARTMENTS d ON d.code = c.faculty
        WHERE     a.RecordID = 205838
              AND a.ORDERTYPE IN (1))
SELECT a.recordid,
       a.course,
       a.year_begin,
       a.stgroup,
       b.strgoup
  FROM temp1 a
       JOIN temp2 b
          ON     a.recordid = b.recordid
             AND a.course = b.course
             AND a.year_begin = b.year_begin;

add two different queries result into one table

If your DBMS support window functions, you may use them to join your intermediate result appropriately.

select t1.id, t1.country, t1.salary, t2.id, t2.country, t2.salary
from
(
  select *, row_number() over (order by id) rn
  from data
  where country = 'us'
) t1
full join
(
  select *, row_number() over (order by id) rn
  from data
  where country = 'uk'
) t2 on t1.rn = t2.rn

demo

RESULT

id      country salary  id  country salary
-------------------------------------------
1       us      10000   2   uk      25000
3       us      35000   4   uk      31000
null    null    null    5   uk      26000

How to combine the results of two different queries into a single query in SQL

Union unions two result sets. You want to have both results, something like a merged.

The following statement isn't proved, but it should show you the idea of the solution. You have to join both, the results and the played games in one query.

select t.name, count(distinct m.id) 'Matches_Won', count(distinct r.id) 'Matches_PLayed' 
from test.team t 
left join test.match_scores m on m.winner = t.id 
left join test.results r on r.home_team = t.id or r.away_team = t.id
group by t.name 
order by Matches_Won, Matches_Played desc; 

How to merge Two queries into one and result in one row

I think you just want OR (or IN) in the WHERE clause:

SELECT lp.lead_bucket_no, 
       SUM(case when p.product = 'S-400' then qty end) as S400,
       SUM(case when p.product = 'Dish Antenna' then qty end) as DishAntenna 
FROM lead_products lp INNER JOIN
     products p
     ON p.product_id = lp.product_id
WHERE type IN ('stock', 'order')
GROUP BY lead_bucket_no
ORDER BY lp.lead_bucket_no;

You also need some sort of aggregation for the expressions that are not in the GROUP BY clause. You may also want to aggregate by TYPE. It is unclear what you want for the final output.

---

Related Topics