Adding columns from other SQL tables

Question

I have 4 tables. These tables are the result of counting the number of entries in another table based on a category. Here's the query used to create these tables:

SELECT Category, COUNT(*) AS Number 
FROM DATABASE.dbo.TABLE
GROUP BY Category;

Like I said, there are 4 of these tables created from different databases. So how could I write a query that ended up with the Category as Column1 but then the next four columns are the count results from the distinct 4 other databases?

EDIT

Incase any of that wasn't clear...

SELECT Category, COUNT(*) AS Number1
FROM DATABASE1.dbo.TABLE
GROUP BY Category;

SELECT Category, COUNT(*) AS Number2
FROM DATABASE2.dbo.TABLE
GROUP BY Category;

SELECT Category, COUNT(*) AS Number3
FROM DATABASE3.dbo.TABLE
GROUP BY Category;

SELECT Category, COUNT(*) AS Number4
FROM DATABASE4.dbo.TABLE
GROUP BY Category;

But what I want is this:

| Category | Number1 | Number2 | Number3 | Number4 |
|----------|---------|---------|---------|---------|
|          |         |         |         |         |
|          |         |         |         |         |

I tried

SELECT DATABASE1.dbo.TABLE.Category, DATABASE1.dbo.TABLE.COUNT(*), DATABASE2.dbo.TABLE.COUNT(*), DATABASE3.dbo.TABLE.COUNT(*), DATABASE4.dbo.TABLE.COUNT(*) 
FROM DATABASE1.dbo.TABLE
JOIN DATABASE2.dbo.TABLE
ON DATABASE1.dbo.TABLE=DATABASE2.dbo.TABLE
JOIN DATABASE3.dbo.TABLE
ON DATABASE2.dbo.TABLE=DATABASE3.dbo.TABLE
JOIN DATABASE4.dbo.TABLE
ON DATABASE3.dbo.TABLE=DATABASE4.dbo.TABLE

But that didn't work.

@scsimon, I looked at the join function but I'm not following what I should join on with these queries or if I can join on something with a count(*) — Neal
– Neal, Commented Feb 7, 2017 at 18:44

Vamsi Prabhala · Accepted Answer · 2017-02-07 19:07:51Z

2

You can use inner joins to do this.

SELECT t1.category,t1.Number1,t2.Number2,t3.Number3,t4.Number4 
FROM (select Category,COUNT(*) AS Number1 from DATABASE1.dbo.TABLE GROUP BY Category) t1
join (select Category,COUNT(*) AS Number2 from DATABASE2.dbo.TABLE GROUP BY Category) t2
on t1.category=t2.category
join (select Category,COUNT(*) AS Number3 from DATABASE3.dbo.TABLE GROUP BY Category) t3
on t2.category=t3.category
join (select Category,COUNT(*) AS Number4 from DATABASE4.dbo.TABLE GROUP BY Category) t4
on t3.category=t4.category

Or a full join if a category isn't common across tables.

SELECT coalesce(t1.category,t2.category,t3.category,t4.category),
coalesce(t1.Number1,0),
coalesce(t2.Number2,0),
coalesce(t3.Number3,0),
coalesce(t4.Number4,0) 
FROM (select Category,COUNT(*) AS Number1 from DATABASE1.dbo.TABLE GROUP BY Category) t1
full join (select Category,COUNT(*) AS Number2 from DATABASE2.dbo.TABLE GROUP BY Category) t2
on t1.category=t2.category
full join (select Category,COUNT(*) AS Number3 from DATABASE3.dbo.TABLE GROUP BY Category) t3
on t2.category=t3.category
full join (select Category,COUNT(*) AS Number4 from DATABASE4.dbo.TABLE GROUP BY Category) t4
on t3.category=t4.category

edited Feb 7, 2017 at 19:07

answered Feb 7, 2017 at 18:52

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Neal Over a year ago

What changed between the cross join and the join? I tried it with a cross join and it seemed to work.

Vamsi Prabhala Over a year ago

yes..if you have multiple categories, cross join would produce many rows which isn;t necessary. join should work just fine if a category has to be present across all the tables. If not, you may have to use a full join.

Gordon Linoff · Accepted Answer · 2017-02-07 18:48:08Z

2

I would suggest union all and aggregation:

select category, sum(t1) as t1, sum(t2) as t2, sum(t3) as t3, sum(t4) as t4
from ((select category, 1 as t1, 0 as t2, 0 as t3, 0 as t4 
       from DATABASE1.dbo.TABLE
      ) union all
      (select category, 0 as t1, 1 as t2, 0 as t3, 0 as t4 
       from DATABASE2.dbo.TABLE
      ) union all
      (select category, 0 as t1, 0 as t2, 1 as t3, 0 as t4 
       from DATABASE3.dbo.TABLE
      ) union all
      (select category, 0 as t1, 0 as t2, 0 as t3, 1 as t4 
       from DATABASE4.dbo.TABLE t
      )
     ) tt
group by category;

answered Feb 7, 2017 at 18:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Akash KC · Accepted Answer · 2017-02-07 19:11:30Z

You can use multiple Common Table Expression(CTE) to get category and count and have join on those multiple CTE.

Your query should be :

with cte1 as
(
select category, count(*) as number1 from DATABASE1.dbo.TABLE group by category
),
cte2 as
(
select category, count(*) as number2 from DATABASE2.dbo.TABLE group by category
),
cte3 as
(
select category, count(*) as number3 from DATABASE3.dbo.TABLE group by category
),
cte4 as
(
select category, count(*) as number4 from DATABASE4.dbo.TABLE group by category
)
select c1.category, c1.number1,c2.number2,c3.number3,c4.number4
from cte1 c1 INNER JOIN cte2 c2 ON c1.category = c2.category  
INNER JOIN cte3 c3 ON c1.category = c3.category
INNER JOIN cte4 c4 ON c1.category = c4.category

I am not sure about the performance of this query but it's an option to have.

Collectives™ on Stack Overflow

Adding columns from other SQL tables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related