Multiple Queries in One Query

Transact-SQL
1

Actually we are fetching Multiple queries for the Dashboard like
We are fetching Total Count Members from Member Table, Total Paid Amount from Receipt Table,
Total Outstanding Payments, Total No. of Unread Messages, Total No. of Todays Birthdays and so on
So, I've written multiple queries in on SP Can I write all these in only single query so that I'll get my output in only 1 row?
Will this affect the performance?

2

This logic totally depends on your database design. without knowing it how we can give an idea about this

3

That's why I wrote that we are fetching data from multiple tables. They don't have relations with each other.
Like
We are counting Total Members from Members Table.
We are counting Total Executives from Executive Table.
Total Payment Received From Receipt Table
Todays Birthdays from Member Table

4

Use everything as subquery in a single select

5

As an example:

DECLARE	@Chuff TABLE
(
	Val CHAR(1) NOT NULL
);
INSERT INTO @Chuff (Val)
VALUES ('A'),('B');

DECLARE	@Chuff1 TABLE
(
	Num INT NOT NULL
);
INSERT INTO @Chuff1 (Num)
VALUES (10),(45);


SELECT	ChuffCount = (SELECT COUNT(*) FROM @Chuff),
		Chuff1Sum  = (SELECT SUM(Num) FROM @Chuff1);
1 Like
6

Ok like
Select
(SELECT COALESCE(COUNT(),0) from Registration ) AS TotCust,
(select COALESCE(COUNT(),0) from AgentMast ) AS TotEx

like this.?
But will it affect on performance of query?

7

If you're running the queries all separately, I can't see that there would be much difference in performance.

The true answer though is to test - duration, execution plans, No. of reads etc. and make your choice based on these.

1 Like
8

define variables for each total counts.
assign the counts to the variable from query.
At the last select the variables
Select @count1, @count2, @count3 likewise

1 Like
9

Thanking you all