asked on

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

ASKER CERTIFIED SOLUTION

viralypatel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

baretree

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

venmarces

ASKER

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