CODEWITHSUNDEEP
sqlms-accesscountsum
xdenama
xdenama

Reputation: 19

Group by and multiple colum count and sum

I have a table listing student grade,

AG          T1    T2     T3
L0011001    A     B       A          
L0011002    A     B       B          
L0011003    A     A       C          
L0011004    A     A       C

I want output for AG like this:

L0011001       2A 1B
L0011002       1A 2B
L0011003       2A 1C
L0011004       2A 1C

How to get this?

Upvotes: 0

Views: 70

Answers (3)

Bhushan Oulkar
Bhushan Oulkar

Reputation: 1

Just see if below query works for you.

`SELECT AG,ALL_VAL,
(LENGTH(ALL_VAL) - LENGTH(REPLACE(ALL_VAL, 'A', '')))  || 'A' || ' '||
(LENGTH(ALL_VAL) - LENGTH(REPLACE(ALL_VAL, 'B', '')))  || 'B' || ' '||
(LENGTH(ALL_VAL) - LENGTH(REPLACE(ALL_VAL, 'C', '')))  || 'C'  as OUTPUT
FROM ( 
SELECT AG,T1||T2||T3 AS ALL_VAL FROM t_s
) RSLT` 

I got the below output using above query.

 - L0011001-->ABA-->    2A 1B 0C
 - L0011002-->ABB-->    1A 2B 0C
 - L0011003-->AAC-->    2A 0B 1C
 - L0011004-->AAC-->    2A 0B 1C

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

You might like to consider a union query and a crosstab, the output is not exactly as you wish, but it does make sense.

TRANSFORM Count(qry.ag) AS countofag
SELECT qry.ag
FROM   (SELECT tbl.ag,
               tbl.t1
        FROM   tbl
        UNION ALL
        SELECT tbl.ag,
               tbl.t2
        FROM   tbl
        UNION ALL
        SELECT tbl.ag,
               tbl.t3
        FROM   tbl) AS qry
GROUP  BY qry.ag
PIVOT qry.t1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Your data is in a really, really bad format. You should have one row per "T" and per student.

However, sometimes we are stuck with bad data formats. You can do what you want with iif():

select ag,
       (iif(t1 = 'A', 1, 0) + iif(t2 = 'A', 1, 0) + iif(t2 = 'A', 1, 0)) as A_s,
       (iif(t1 = 'B', 1, 0) + iif(t2 = 'B', 1, 0) + iif(t2 = 'B', 1, 0)) as B_s,
       (iif(t1 = 'C', 1, 0) + iif(t2 = 'C', 1, 0) + iif(t2 = 'C', 1, 0)) as C_s
from t;

This doesn't do exactly what you want. It puts the values into separate columns -- a format that makes more sense to me.

For your specific format:

select ag,
       (iif(A_s > 0, A_s & "A ") &
        iif(B_s > 0, B_s & "B ") &
        iif(C_s > 0, C_s & "C ")
       )
from (select ag,
             (iif(t1 = 'A', 1, 0) + iif(t2 = 'A', 1, 0) + iif(t2 = 'A', 1, 0)) as A_s,
             (iif(t1 = 'B', 1, 0) + iif(t2 = 'B', 1, 0) + iif(t2 = 'B', 1, 0)) as B_s,
             (iif(t1 = 'C', 1, 0) + iif(t2 = 'C', 1, 0) + iif(t2 = 'C', 1, 0)) as C_s
      from t
     ) as x

Upvotes: 3

Related Questions