SQL query

[GlenMcCabe]

In a database table I inherited the structure contains 3 rows concern1, concern2, concern3.

Record 1 may have concern1 A, concern2 B (nothing in concern3)
Record 2 may have concern1 C, concern2 A (nothing in concern3)
Record 3 may have concern1 B, concern2 A , concern3 D

I would like to end up with

Concern Count
A 3
B 2
C 1
D 1

My knowledge of SQL is not enough to do this.

Glen

[kev1n]

contains 3 row

Do you mean 3 columns?

BTW, questions that are not specific to nuBuilder can also be asked at stackoverflow. There are thousands of active users.

[GlenMcCabe]

Yes Kevin I mean 3 columns

I will try stackoverflow, Apologies for misposting.

[GlenMcCabe]

I was able to get what I wanted using a temporary table. Stackoverflow gave me a neater way using UNION ALL.

Problem solved

[kev1n]

SQL from stackoverflow as future reference:

Code: Select all

SELECT Concern, COUNT(*) AS Count
FROM
(
    SELECT concern1 AS Concern FROM yourTable UNION ALL
    SELECT concern2 FROM yourTable UNION ALL
    SELECT concern3 FROM yourTable
) t
WHERE
    Concern IS NOT NULL
GROUP BY
    Concern;

[GlenMcCabe]

The response from stackoverflow works and I have been able to extend it to include the scores held against each score. I can now use this logic to chart number of each concern in a period. The difference between the totals of the pre and post assessments of each concern. The difference between the average of the pre and post concerns. At some stage I will use it toproduce a report that lists every occurrence.

Thanks for pointing me to stackoverflow Kevin. They have helped with another awkward SQL requirement.