CODEWITHSUNDEEP
sql-serversql-server-2012
Marqueone
Marqueone

Reputation: 1213

Optimizing a dashboard query

I'm building an application and in this app I want to show simple statistics about the number of items in the system and in their various states and I've created a query that while it works I fell as though its incredibly inefficient, but to be honest I'm not sure how to go about making it better.

What I have ended up with is a cute that is nothing more than a bunch of sub selects in order to get my dashboard stats as such:

with
cte_dashboard(active_employees, inactive_employees, linked_employees, total_employees, 
                ongoing, completed, pending, cancelled, total_contracts) as
(
    select 
        (select count(id) from [tam].[employees] where isActive = 1) as 'active',
        (select count(id) from [tam].[employees] where isActive = 0) as 'inactive',
        (select count(id) from [tam].[employees] where UserId is not null) as 'linked',
        (select count(id) from [tam].[employees]) as 'total',
        (select count(id) from [tam].[contracts] where Status = 'ongoing') as 'ongoing',
        (select count(id) from [tam].[contracts] where Status = 'completed') as 'completed',
        (select count(id) from [tam].[contracts] where Status = 'pending') as 'pending',
        (select count(id) from [tam].[contracts] where Status = 'cancelled') as 'cacnelled',
        (select count(id) from [tam].[contracts]) as 'total'
)    

select * from cte_dashboard;

How can I make this better without all of these subqueries, or is this really all there is to do?

Upvotes: 0

Views: 92

Answers (3)

Kaf
Kaf

Reputation: 33839

Something like this:

;WITH Emp AS (
    SELECT 1 RowId, 
           SUM(1) AS Total, 
           SUM(CASE WHEN IsActive = 1 THEN 1 END) Active,
           SUM(CASE WHEN IsActive = 0 THEN 1 END) Inactive,
           SUM(CASE WHEN UserId IS NOT NULL THEN 1 END) Linked
    FROM Employees
),
Con AS (
    --similar query to Contracts table
)
SELECT *
FROM Emp e
      JOIN Con c ON e.RowId = c.RowId

Upvotes: 1

Deep
Deep

Reputation: 3202

This might also work :

SELECT * FROM
(
    SELECT 
        SUM(CASE WHEN isActive = 1 THEN 1 ELSE 0 END) AS [Active],
        SUM(CASE WHEN isActive = 0 THEN 1 ELSE 0 END) AS [Inactive],
        SUM(CASE WHEN UserId IS NOT NULL THEN 1 ELSE 0 END) AS [linked],
        COUNT(*) AS [total] -- OR SUM(1)
    FROM [tam].[employees]
) a
CROSS JOIN
(
    SELECT 
        SUM(CASE WHEN Status = 'ongoing' THEN 1 ELSE 0 END) AS [ongoing],
        SUM(CASE WHEN Status = 'completed' THEN 1 ELSE 0 END) AS [completed],
        SUM(CASE WHEN Status = 'pending' THEN 1 ELSE 0 END) AS [pending],
        SUM(CASE WHEN Status = 'cancelled' THEN 1 ELSE 0 END) AS [cancelled],
        COUNT(*) AS [total] -- OR SUM(1)
    FROM [tam].[contracts]
) b

Upvotes: 2

Alec.
Alec.

Reputation: 5535

You could do the counts with a partition

COUNT(ID) OVER(Partition By [tam].[employees].isActive  Group By IsActive)
COUNT(ID) OVER(Partition By [contracts].Status Group By Status)

Something like that, looks a lot cleaner.

Upvotes: 0

Related Questions