CODEWITHSUNDEEP
sqlsql-server
Ryan
Ryan

Reputation: 155

SQL COUNT Rows Result in separate columns

I currently use this to count all the number or rows in a table and it works well for what i need. 

SELECT COUNT(*) AS STCount 
  FROM  (SELECT Distinct DPoint, RNum 
           FROM ECount  
          WHERE DType = 'STR' AND Month(EDate) = '07') AS rows

The only issue i have is i have to repeat this statement a lot while just changing a few things each time. I would like to Union these Selects but have the result show in a seperate column, the example below works getting the results but puts them all in the same column. Any ideas how to have them show in their own column one for STCount and NCCount?

SELECT COUNT(*) AS STCount 
  FROM (SELECT Distinct DPoint, RNum 
          FROM ECount  
         WHERE DType = 'STR' AND Month(EDate) = '07') AS rows 
 UNION 
SELECT COUNT(*) AS NCCount 
  FROM (SELECT Distinct DPoint, RNum 
          FROM ECount  
         WHERE DType = 'NCD' AND Month(EDate) = '07') AS rows

result would be,

STCount NCCount
  100     202

Upvotes: 8

Views: 5263

Answers (7)

Richard
Richard

Reputation: 109005

You can do selects in the select clause and don't need a from.

select (select count(1)
        from  (select distinct DPoint, RNum 
               from ECount
               where DType = 'STR' 
                 and Month(EDate) = '07') as x
       ) as rows1,
      (select count(1)
       from  (select distinct DPoint, RNum 
              from ECount  
              where DType = 'NCD' 
                and Month(EDate) = '07') as x
      ) as rows2;

Upvotes: 12

RealCheeseLord
RealCheeseLord

Reputation: 795

SELECT (SELECT COUNT(*) AS STCount 
          FROM (SELECT Distinct DPoint, RNum 
                  FROM ECount  
                 WHERE DType = 'STR' AND Month(EDate) = '07')) AS STCount
       , (SELECT COUNT(*) AS NCCount 
           FROM (SELECT Distinct DPoint, RNum 
                   FROM ECount  
                  WHERE DType = 'NCD' AND Month(EDate) = '07')) AS NCCount

Upvotes: 4

SQB
SQB

Reputation: 4078

A more practical approach might be selecting each type of count as a separate record and use grouping:

SELECT
   COUNT(*) AS TypeCount,
   Dtype
FROM ECount
WHERE Dtype IN ('STR', 'NCD')
  AND MONTH(Edate) = '07'
GROUP BY Dtype;

Upvotes: 0

Peter Abolins
Peter Abolins

Reputation: 1539

Basically, you just need to provide a placeholder for each of the columns in your union statement:

SELECT COUNT(*) AS STCount, 0 AS NCCount 
FROM (SELECT DISTINCT DPoint, RNum 
      FROM ECount
      WHERE DType = 'STR' AND Month(EDate) = '07') AS rows 
UNION SELECT 0, COUNT(*) 
FROM (SELECT DISTINCT DPoint, RNum 
      FROM ECount
      WHERE DType = 'NCD' AND Month(EDate) = '07') AS rows

The above doesn't conform to the desired output which was supplied after I added my answer. So, I would suggest you go with one of the other answers (e.g., the one by Richard).

Upvotes: 2

TOUZENE Mohamed Wassim
TOUZENE Mohamed Wassim

Reputation: 712

You can use a CASE :

SELECT 
    COUNT (CASE WHEN DType = 'STR' THEN (1)  ELSE NULL END) AS STCount,
    COUNT (CASE WHEN DType = 'NCD' THEN (1)  ELSE NULL END) AS NCCount
    FROM (Select Distinct DType, DPoint, RNum From ECount WHERE Month(EDate) = '07') as rows

Upvotes: 7

ASpirin
ASpirin

Reputation: 3651

select sum(x.[STR]) as STCount, sum(x.[NCD]) as NCCount
from ECount  
pivot (count(DType) for DTypein ([NCD], [STR])) as x

Upvotes: 2

tukan
tukan

Reputation: 17347

I think you nearly got it use UNION ALL instead of UNION:

SELECT DType, COUNT(*) FROM (
    SELECT DType From  (Select Distinct DPoint, RNum From
    ECount  Where DType = 'STR' and Month(EDate) = '07') as rows 
    UNION ALL
    SELECT DType From  (Select Distinct DPoint, RNum From
    ECount  Where DType = 'NCD' and Month(EDate) = '07') as rows
)
GROUP BY DType
ORDER BY DType

for more on UNION aUNION ALL VS. UNION

Upvotes: 2

Related Questions