CODEWITHSUNDEEP
sql-serverselectunion
Code
Code

Reputation: 2129

2 Select Statements, 1 Query -- Return 2 Columns

I have 2 select statements that I would like to return in 1 query. I am currently using union, and it is returning both results in 1 query, but it is returning 2 rows...

I am looking to try and return 2 columns, not 2 rows.

Below is my query:

SELECT distinct count([number]) AS 'Total' from [myTbl] WHERE [type] = 'online' union
SELECT distinct count([number]) AS 'Success' from [myTbl] WHERE [type] = 'online' and [MyValue] = 'true'

I would like 2 columns.. Total and Success. Is this possible? Maybe not through union, but some other methodology?

Upvotes: 2

Views: 3844

Answers (5)

Cee McSharpface
Cee McSharpface

Reputation: 8735

You can wrap it in two subqueries, so you will get one row with two result columns.

SELECT
  (SELECT distinct count([number]) FROM [myTbl] WHERE [type]='online') AS Total,
  (SELECT distinct count([number]) FROM [myTbl] WHERE [type]='online' AND [MyValue]='true') AS Success

Note that the use of DISTINCT is doubtful here.

Upvotes: 5

Володин Андрей
Володин Андрей

Reputation: 440

Try this

    SELECT 
       (SELECT count([number]) FROM [myTbl] WHERE [type] = 'online') AS 'Total',
       (SELECT count([number]) FROM [myTbl] WHERE [type] = 'online' and [MyValue] = 'true') AS 'Success'

Or a bit more maintainable

    ;WITH tbl as (
        SELECT [number], [MyValue] FROM [myTbl] WHERE [type] = 'online'
    )
    SELECT 
       (SELECT count([number]) FROM tbl) AS 'Total',
       (SELECT count([number]) FROM tbl WHERE [MyValue] = 'true') AS 'Success'

Upvotes: 0

David M
David M

Reputation: 72930

How about this? It sums a value for each row, set to 1 for success and 0 otherwise, which therefore counts the successes.

SELECT  COUNT([number]) AS 'Total',
        SUM(CASE WHEN [MyValue] = 'true' THEN 1 ELSE 0 END) AS 'Success'
FROM    [myTbl]
WHERE   [type] = 'online'

Upvotes: 1

victorayub
victorayub

Reputation: 67

It's simple, do it like this:

SELECT Total, Success from 
(
    SELECT distinct count([number]) AS 'Total' from [myTbl] WHERE [type] = 'online' union
    SELECT distinct count([number]) AS 'Success' from [myTbl] WHERE [type] = 'online' and [MyValue] = 'true'
)

Upvotes: -1

Chris Pickford
Chris Pickford

Reputation: 9001

Perfect use case for Common Table Expressions.

;WITH CTE_Total AS (
    SELECT DISTINCT COUNT([number]) AS 'Total'
    FROM [myTbl]
    WHERE [type] = 'online'
), CTE_Success AS (
    SELECT DISTINCT COUNT([number]) AS 'Success'
    FROM [myTbl]
    WHERE [type] = 'online' and [MyValue] = 'true'
)
SELECT
    [Total] = CTE_Total.Total,
    [Success] = CTE_Success.Success;

Upvotes: 0

Related Questions