Combining the Results of Two SQL Queries as Separate Columns

Combining the results of two SQL queries as separate columns

You can aliasing both query and Selecting them in the select query

http://sqlfiddle.com/#!2/ca27b/1

SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y

Combining the results from two separate queries in to two columns

You can use combine two sub-queries in one query like this:

select
(select count(*) from Inventory i, Sale s where i.vin=s.vin and i.condition='new') as New,
(select count(*) from Inventory i, Sale s where i.vin=s.vin and i.condition='used') as Used

What were you trying to achieve with order by statement?

How can I combine two SQL queries returning multiple columns into separate columns of the same table?

Since MySQL treats booleans as 1 or 0 in a numeric context, you can just SUM the result of comparing actual and result to get your required columns:

SELECT MONTHNAME(ts) as mnth, 
       SUM(actual = result) AS correct_predictions,
       SUM(actual != result) AS incorrect_predictions
FROM prediction 
GROUP BY mnth;

Note that using MONTHNAME will result in values from every year being grouped together (e.g. May 2019 with May 2020). That may be what you want (or perhaps you are restricting the range of the query with a WHERE clause) but if not, you should use something like

EXTRACT(YEAR_MONTH FROM ts) AS mnth

to include the year in the value you are grouping by.

How to combine two SQL queries in MySQL with different columns without combining their resulting rows

This seems like MySQL. You could do something like this:

select * from (SELECT articles.article_id as id_article_comment, articles.title as title_message, articles.date as created, 'article' AS contenttype, articles.article_id as article_id, articles.idUsers, users.uidUsers FROM articles JOIN users ON articles.idUsers = users.idUsers WHERE articles.idUsers = '1' AND articles.published = 'yes' ORDER BY articles.date DESC LIMIT 5) a
union all
select * from (SELECT articlecomments.comment_id, articlecomments.message, articlecomments.date, 'article comment' AS contenttype, articlecomments.article_id, articlecomments.idUsers, users.uidUsers FROM articlecomments JOIN users ON articlecomments.idUsers = users.idUsers WHERE articlecomments.idUsers = '1' ORDER BY articlecomments.date DESC LIMIT 5) b
order by created DESC

See example here: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=26280a9c1c5f62fc33d00d93ab84adf3

Result like this:

id_article_comment | title_message  | created             | article_id | uidUsers   
-----------------: | :------------- | :------------------ | ---------: | :----------
                 1 | first article  | 2020-07-09 05:59:18 |          1 | genericUser
                 2 | second article | 2020-07-09 05:59:18 |          1 | genericUser
                 3 | third article  | 2020-07-09 05:59:18 |          1 | genericUser
                 1 | first message  | 2020-07-09 05:59:18 |          1 | genericUser
                 2 | second message | 2020-07-09 05:59:18 |          1 | genericUser
                 3 | third message  | 2020-07-09 05:59:18 |          1 | genericUser

Explanation

Since we want to use order by and limit, we'll create a subquery out of the first line and select all columns from that first subquery. We'll name each field the way we want in the output.

We do the same thing with the 2nd query and add a union all clause between them. Then, we apply ordering based on created date (which was an alias in the first query) to get the results you desired in the order you desired.

If you use union, duplicate rows will be eliminated from the result. If you use union all, duplicate rows - if they exist - will be retained. union all is faster since it combines 2 datasets (as long as columns are same in queries. union has to, additionally, look for duplicate rows and remove them from the query.

Combine two separate SQL queries in a single query

What you are showing is still two separate results. One query gives you one result. If you want to combine the two queries that gives one query and one result. One method:

SELECT what, name, value
FROM
(
  SELECT 'INCOME' as what, name, income as value, 1 as sortkey1, -income as sortkey2
  FROM table1
  WHERE income > 150
  UNION ALL
  SELECT 'OUTCOME' as what, name, outcome as value, 2 as sortkey1, outcome as sortkey2
  FROM table2
  WHERE outcome < 200
)
ORDER BY sortkey1, sortkey2;

How to combine two separate sql queries into two separate columns?

Instead of union all you need to join both the table on name column then select second columns individually from both tables. I am considering the fact that name columns are same as both tables. If there is any name present in one table and absent in another it will not be shown in the result

select t1.name,t1.count ,t2."Opt-in Rate (Sent)" ,t1.count*t2."Opt-in Rate (Sent)" as predicted
from (
SELECT "source"."name" AS "name", "source"."count" AS "count"

FROM (SELECT "source"."count" AS "count", "source"."name" AS "name", "source"."count" AS "count_2", ("source"."count" * 1.5) AS "a" FROM (SELECT "marketing_campaign__via__campa"."name" AS "name", count(*) AS "count" FROM "public"."event_event"
LEFT JOIN "public"."event_event" "Event Event - Source Event" ON "public"."event_event"."source_event_id" = "Event Event - Source Event"."id" LEFT JOIN "public"."marketing_campaign" "marketing_campaign__via__campa" ON "public"."event_event"."campaign_id" = "marketing_campaign__via__campa"."id"
WHERE "public"."event_event"."status" = 'Queued'
GROUP BY "marketing_campaign__via__campa"."name"
ORDER BY "marketing_campaign__via__campa"."name" ASC) "source") "source") t1 inner join
(
SELECT marketing_campaign.name,

    cast(sum((event_event.status='Opt-in')::int) as decimal) / nullif(sum((event_event.status='Sent')::int), 0)* 100 as "Opt-in Rate (Sent)"
FROM event_event
JOIN marketing_campaign ON event_event.campaign_id = marketing_campaign.id
WHERE marketing_campaign.is_archived=false [[AND {{date_created}}]]
GROUP BY marketing_campaign.name
)t2 on t1.name=t2.name
LIMIT 1048576

combining results of two select statements

You can use a Union.

This will return the results of the queries in separate rows.

First you must make sure that both queries return identical columns.

Then you can do :

SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS Number
FROM tableD 
RIGHT OUTER JOIN [tableB] 
INNER JOIN tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id 
GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tableB].CompanyImageUrl

UNION

SELECT tableA.Id, tableA.Name,  '' AS Owner, '' AS ImageUrl, '' AS CompanyImageUrl, COUNT([tableC].Id) AS Number
FROM 
[tableC] 
RIGHT OUTER JOIN tableA ON [tableC].tableAId = tableA.Id GROUP BY tableA.Id, tableA.Name

As has been mentioned, both queries return quite different data. You would probably only want to do this if both queries return data that could be considered similar.

SO

You can use a Join

If there is some data that is shared between the two queries. This will put the results of both queries into a single row joined by the id, which is probably more what you want to be doing here...

You could do :

SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS NumberOfUsers, query2.NumberOfPlans
FROM tableD 
RIGHT OUTER JOIN [tableB] 
INNER JOIN tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id 


INNER JOIN 
  (SELECT tableA.Id, COUNT([tableC].Id) AS NumberOfPlans 
   FROM [tableC] 
   RIGHT OUTER JOIN tableA ON [tableC].tableAId = tableA.Id 
   GROUP BY tableA.Id, tableA.Name) AS query2 
ON query2.Id = tableA.Id

GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tableB].CompanyImageUrl

---

Related Topics