CODEWITHSUNDEEP
sqlms-access
yehya Isa
yehya Isa

Reputation: 35

how to count repeation of value from multiple columns?

I have access table (called year2016) with months columns and client name rows and the worker name who did the jop in this month. i want sql code that give me how many clients every worker dealed with by month.

the table look like this: 

client   month01    month02     month03   month04 .......
1         john       adam        sarah      john
2         adam        adam        john      sarah
.
.
.

I am new to sql i tried this (meanwhile for two months) but it give me error:

SELECT a.name, c1.count, c2.count
FROM (SELECT month01 AS name FROM year2016 UNION SELECT month02 FROM year2016) a
LEFT JOIN (SELECT month01, COUNT(*) AS count FROM year2016 GROUP BY month01) c1
ON a.name=c1.month01
LEFT JOIN (SELECT month02, COUNT(*) AS count FROM year2016 GROUP BY month02) c2
On a.name=c2.month02;

I expect to get table like this:

worker     month01     month02    month03    month04 ......
john           8           14        5          9
adam           12            6          5        3
sarah           4            6         34        56

Upvotes: 0

Views: 38

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

One approach might be to first union together all month columns into a single column, and then pivot by month:

SELECT
    worker,
    SUM(IIF(month='month01', 1, 0)) AS month01,
    SUM(IIF(month='month02', 1, 0)) AS month02,
    SUM(IIF(month='month03', 1, 0)) AS month03,
    SUM(IIF(month='month04', 1, 0)) AS month04,
    SUM(IIF(month='month05', 1, 0)) AS month05,
    SUM(IIF(month='month06', 1, 0)) AS month06,
    SUM(IIF(month='month07', 1, 0)) AS month07,
    SUM(IIF(month='month08', 1, 0)) AS month08,
    SUM(IIF(month='month09', 1, 0)) AS month09,
    SUM(IIF(month='month10', 1, 0)) AS month10,
    SUM(IIF(month='month11', 1, 0)) AS month11,
    SUM(IIF(month='month12', 1, 0)) AS month12
FROM
(
    SELECT month01 AS worker, 'month01' AS month FROM year2016 UNION ALL
    SELECT month02, 'month02' FROM year2016 UNION ALL
    SELECT month03, 'month03' FROM year2016 UNION ALL
    SELECT month04, 'month04' FROM year2016 UNION ALL
    SELECT month05, 'month05' FROM year2016 UNION ALL
    SELECT month06, 'month06' FROM year2016 UNION ALL
    SELECT month07, 'month07' FROM year2016 UNION ALL
    SELECT month08, 'month08' FROM year2016 UNION ALL
    SELECT month09, 'month09' FROM year2016 UNION ALL
    SELECT month10, 'month10' FROM year2016 UNION ALL
    SELECT month11, 'month11' FROM year2016 UNION ALL
    SELECT month12, 'month12' FROM year2016
) t
GROUP BY
    worker;

But note that your data is not normalized at all. The subquery I have aliased as t above is really how you should be storing your data in Access. So, try to change your design if you can.

Upvotes: 2

Related Questions