Page 1 of 1
SQL query
Posted: Sun Aug 22, 2021 11:45 pm
by 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
Re: SQL query
Posted: Mon Aug 23, 2021 7:50 am
by 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.
Re: SQL query
Posted: Mon Aug 23, 2021 8:28 am
by GlenMcCabe
Yes Kevin I mean 3 columns
I will try stackoverflow, Apologies for misposting.
Re: SQL query
Posted: Mon Aug 23, 2021 11:42 am
by GlenMcCabe
I was able to get what I wanted using a temporary table. Stackoverflow gave me a neater way using UNION ALL.
Problem solved
Re: SQL query
Posted: Mon Aug 23, 2021 12:42 pm
by 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;
Re: SQL query
Posted: Wed Sep 01, 2021 5:30 pm
by 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.