Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
id x1 x2
1 null 0
2 1 1
3 1 null
4 0 null
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
SELECT
COUNT(CASE WHEN x1 = 1 THEN 1 END) AS x1_1
, COUNT(x1) AS x1
, CASE WHEN COUNT(x1) > 0 THEN
COUNT(CASE WHEN x1 = 1 THEN 1 END) * 100.0 / COUNT(x1)
ELSE 0
END AS x1_pct
, COUNT(CASE WHEN x2 = 1 THEN 1 END) AS x2_1
, COUNT(x2) AS x2
, CASE WHEN COUNT(x2) > 0 THEN
COUNT(CASE WHEN x2 = 1 THEN 1 END) * 100.0 / COUNT(x2)
ELSE 0
END AS x2_pct
FROM table1
;
The aggregate function COUNT() will only count non-null values (in other words it ignores nulls)| X1_1 | X1 | X1_PCT | X2_1 | X2 | X2_PCT |
|------|----|-----------------|------|----|--------|
| 2 | 3 | 66.666666666666 | 1 | 2 | 50 |
But do remember that divide by zero isn't allowed so if COUNT(X1) is zero you cannot attempt the percentage calculationselect (count(x1)*1.0 / count(1))*100 as PercentX1, (count(x2)*1.0 / count(1))*100 as PercentX2
from YourTableNameHere
SELECT
CASE WHEN COUNT(x1) > 0 THEN
COUNT(CASE WHEN x1 = 1 THEN 1 END) * 100.0 / COUNT(x1)
ELSE 0
END AS x1_pct
, CASE WHEN COUNT(x2) > 0 THEN
COUNT(CASE WHEN x2 = 1 THEN 1 END) * 100.0 / COUNT(x2)
ELSE 0
END AS x2_pct
FROM table1
;
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
question about results where i dont have a match | 3 | 36 | |
SQL Database Restore 2008 R2 | 1 | 27 | |
MS SQL Conditional WHERE clause | 3 | 38 | |
MSSQL Convert Char to Date Time | 5 | 13 |
Join the community of 500,000 technology professionals and ask your questions.