Query to find the Percentage of items in a field

[RaveendiraN RR]

Hi,

I have a user database as shown
User Score
u1 1
u2 2
u1 4
u3 7
u2 1
u1 6
u2 5
u1 2

I want a query that would give me the result where i would get the
percentage break up scores by the each user for a particluar score, say U1
has 25% of scores as 1 (formula= count the number of times the user obatined
the score/ total times the user name has appeared in the database)

Further more i want to group the based on scores between 1 and 4, 5 and 7,
8 and 9 gining the percentage break up by user for these groups

Example:
User Group1 Group2 Group3
U1 75% 25% 0%

How can this be achieved in one query?
If this is not possible in a single query, how can this be achieved ? Please
help !!!


Thanks,
Raveendiran RR
Mail: (e-mail address removed)

 

[Gummball]

For your first question, try this SQL to get to the counts:

TRANSFORM Count(tblName.Score) AS CountOfScore
SELECT tblName.User
FROM tblName
GROUP BY tblName.User
PIVOT tblName.Score;

You can then join that query to one which divides each cell by the total for
that row if you're interested in getting out percentages. Try a similar
approach to answer your second question (add 1,2,3,4 counts and divide by all
counts).

Hope that helps!

 

[KARL DEWEY]

I could not do it in one query but did in two --
Raveendira_Crosstab --
TRANSFORM Sum(IIf([Score] Between Val(Partition([Score],1,9,3)) And
Val(Right(Partition([Score],1,9,3),1)),1,0)) AS Expr1
SELECT Raveendira.User, Count(Raveendira.Score) AS Count_of_Scores
FROM Raveendira
GROUP BY Raveendira.User
PIVOT Partition([Score],1,9,3);

SELECT Raveendira_Crosstab.User, Raveendira_Crosstab.Count_of_Scores, [1:
3]/[Count_of_Scores] AS [Score 1-3], [4: 6]/[Count_of_Scores] AS [Score 4-6],
[7: 9]/[Count_of_Scores] AS [Score 7-9]
FROM Raveendira_Crosstab;

 

[vanderghast]

SELECT user,
switch( score <=4, "Group1", score <=7, "Group2", true, "Group3"),
count(*)/(SELECT COUNT(*) FROM tableName AS a WHERE a.user = b.user)
FROM tableName AS b
GROUP BY user,
switch( score <=4, "Group1", score <=7, "Group2", true, "Group3")



Note that the result is still vertical:

u1 Group1 0.75
u1 Group2 0.25
u2 Group1 0.67
u2 Group2 0.33
u3 Group2 1.00


Vanderghast, Access MVP