question

matta1985 avatar image
matta1985 asked

SQl query

how to get the count from two different tables based on some where condition with single query
sql query
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please supply some more details about your tables and the condition you want to apply
2 Likes 2 ·
This site works on the concept of votes. For each of the answers below that was helpful you should indicate this by clicking on the thumbs up next to those answers. If anyone of the answers below helped solve your problem you can indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Without an example I can only offer general advice. In order to get a count from each table, you'll need to query each table individually. However, you can combine the output. One mechanism would be to use a [UNION query][1]. [1]: http://msdn.microsoft.com/en-us/library/ms180026.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
You could create a view over the two tables and query that for the rowcount... Assuming the tables have compatible fields (or you can make an appropriate subset of fields). eg: CREATE VIEW MyView AS SELECT ID, Field1, Field2, CreatedDate FROM Table1 UNION SELECT ID, Field3 AS Field1, Field4 AS Field2, CreatedDate FROM Table2 and then SELECT COUNT(*) FROM MyView WHERE CreatedDate >= '2013-01-01' AND CreatedDate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
Grant and Thomas answers are correct, but you need to use UNION ALL to get all records from both tables, otherwise you'll get only distinct records. UNION is often abused where UNION ALL can be used to increase performance.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

100% correct. Good addition. Without details, hard to know if it's what's needed or not.
0 Likes 0 ·
dvroman avatar image
dvroman answered
There are multiple ways to do this. You can UNION the two tables together and do the condition on that if you're looking for a single answer. Another method, if you're looking for a comparison is to use sub-queries. SELECT (SELECT COUNT(*) FROM TableA WHERE Condition) TableA, (SELECT COUNT(*) FROM TableB WHERE Condition) TableB
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.