CODEWITHSUNDEEP
sqlms-access-2007
John
John

Reputation: 405

SQL Percentage Count Query By Date

I am able to calculate the percentage count on a particular date in a Microsoft Access 2007 SQL query using:

SELECT Date, Val, (Count(Val) / (SELECT Count(*) From Table HAVING Date=#7/31/2012#) as PercentVal    
FROM Table  
GROUP BY Date, Val  
HAVING Date=#7/31/2012#

However, I would like to make this same calculation over every date using the count totals . For instance, the query:

SELECT Date, Val, Count(*) AS CountVal  
FROM Table  
GROUP BY Date, Val

finds the counts in every period. I would like to add an additional column with the percent counts. However, I can't seem to figure out how to calculate count percentage in every period without using the above block of text and setting up queries for each individual period.

Upvotes: 1

Views: 1049

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

You can subquery it like this:

SELECT A.ADate, A.Val, COUNT(A.Val) / B.DateCount
FROM Table1 AS A
INNER JOIN (
    SELECT C.ADate, COUNT(*) AS DateCount
    FROM Table1 C
    GROUP BY C.ADate
) AS B ON A.ADate = B.ADate
GROUP BY A.ADate, A.Val, B.DateCount

Upvotes: 1

Related Questions