sql subqueries, grouping and a bit of math

Question

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 MyTable
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]

RE: "how could I show percentage in another row" - you mean another **column** presumably? — Martin Smith, Dec 02 '10 at 16:58
@Daniel: It would be nice to see a results mockup. E.g. what do you expect the output to look like? — Paul Sasik, Dec 02 '10 at 17:05
@Daniel I restored my answer. Since you included it in your question. You should note I changed the alias on Percent because it caused a syntax error — Conrad Frix, Dec 02 '10 at 18:12
Yes, I meant to correct that, but [Percentage] works as-well — Daniel, Dec 02 '10 at 18:20

score 2 · Accepted Answer · answered Dec 02 '10 at 17:38

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;

sql subqueries, grouping and a bit of math

1 Answers1