sql subqueries, grouping and a bit of math
this counts all items in B and groups on A:
SELECT A, Count(*) AS [Count All]
FROM MyTable
GROUP BY A;
this counts all 1s in B and groups on A:
SELECT A, Count(*) AS [Count Ones]
FROM My开发者_C百科Table
WHERE
MyTable.[B]='1'
GROUP BY A;
How do I put both columns (all and 1s) and additionally, how could I show percentage in another row
table columns:
[A] [COUNT ALL] [COUNT ONES] [ONES/ALL]
Since its MS-ACCESS you can use iif instead of case
SELECT
Table1.A,
Sum(IIf([B]=1,1,0)) AS Count1,
Count(Table1.A) AS total,
(Sum(IIf([B]=1,1,0))/ Count(Table1.A)) * 100 AS percentofones
FROM
Table1
GROUP BY
Table1.A;
精彩评论