We help IT Professionals succeed at work.

Build SQL Query with an union of theree simple queries

Hi

I have a table called Table1 that contains a column called DateCreation and a column called Field1 ... I want to build a view within a Query that will privide me 3 calculated columns depending on a Field1 value. This Query is a combination of theree queries.

FirstOne  

SELECT Count(ID) AS Total
FROM Table1

SecondOne

SELECT Count(ID)/ Total * 100 AS FirstPercentage
FROM Table1
WHERE Field1 = True AND DateCreation >= GetDate() - 90

ThirdOne

SELECT Count(ID)/ Total * 100 AS SecondPercentage
FROM Table1
WHERE Field1 = True AND DateCreation >= GetDate() - 180

How I can bbuild this query within one single query and getting Total, FirstPercentage, SecondPercentage columns

Thanks


Comment
Watch Question

if you want all three in different columns use this:

SELECT Count(a.ID) AS Total ,Count(a.ID)/ Count(a.ID) * 100 AS FirstPercentage, Count(b.ID)/ Count(a.ID) * 100 AS SecondPercentage
FROM Table1 a left join Table1 b
on a.ID = b.ID
where a.Field1 = True
and a.DateCreation >= GetDate() - 90 
and b.DateCreation >= GetDate() - 180 

Open in new window


if you want all three in the same column use this:

SELECT Count(ID) AS Total 
FROM Table1 

union all 

SELECT Count(ID)/ Total * 100 AS FirstPercentage 
FROM Table1
WHERE Field1 = True AND DateCreation >= GetDate() - 90 

union all

SELECT Count(ID)/ Total * 100 AS SecondPercentage 
FROM Table1
WHERE Field1 = True AND DateCreation >= GetDate() - 180 

Open in new window

Commented:
something like this
(and you can play with partition, etc) :


select	tot = max(total),
	FirstPercentage = cast((100 * sum( case when DateCreation >= GetDate() - 90 then 1.0 else 0.0 end ))/max(total) as numeric (6,3)), 
	SecondPercentage = cast((100 * sum( case when DateCreation >= GetDate() - 180 then 1.0 else 0.0 end ))/max(total) as numeric (6,3))
from (	select	total = ( select count(1) from table1 ),
		Field1,
		DateCreation
		from	table1 ) abc 
where	Field1 = 1;

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

SELECT
    COUNT(ID) AS Total,
    SUM(CASE WHEN Field1 = True AND DateCreation >= GetDate() -  90 THEN 1 ELSE 0 END) / COUNT(ID) * 100 AS FirstPercentage,
    SUM(CASE WHEN Field1 = True AND DateCreation >= GetDate() - 180 THEN 1 ELSE 0 END) / COUNT(ID) * 100 AS SecondPercentage
FROM Table1

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
That is purely integer arithmetic and so may yield zero rather than a percentage.

I suggest adjusting the computations slightly, like so:

CAST(SUM(CASE WHEN Field1 = True AND DateCreation >= GetDate() -  90 THEN 1 ELSE 0 END) * 1.0 / COUNT(ID) * 100 AS decimal(4, 1)) AS FirstPercentage,
    CAST(SUM(CASE WHEN Field1 = True AND DateCreation >= GetDate() - 180 THEN 1 ELSE 0 END) * 1.0 / COUNT(ID) * 100 AS decimal(4, 1)) AS SecondPercentage

Open in new window

Author

Commented:
Thanks guys for your help all solutions you provided are working well