Last post 19 hours, 56 minutes ago by yij sun
Apr 07, 2021 04:22 PM|dl0dth|LINK
I'm trying to do a count on a column, count the number of other columns that are associated with it, and after the count (as var), get the column data as it is distinct to the count of them.
So in the case of a query on a table that has tons of columns, I'm only interested in a column called Sponsor, the count of Investigators that match the Sponsor, AND also the name of the Investigator in one SQL query. I can get the count on Sponsor with
the GROUP BY on Sponsor, with a count of Investigators that are associated with them, but along with the count, I need to see the
single investigator associated with the count. All this data is in one table.
I can get:
Sponsor | Investigators
I need to get:
Sponsor | Investigators | Investigator
Sponsor1 13 Investigator9 (in the table 13 times, etc.)
Sponsor2 4 Investigator7
Sponsor3 1 Investigator2
The complexity is happening by not being able to get the DISTINCT investigator for the count which as you can see would "whittle" the list of multiplicity of these investigators down to 1 even though there could be many associated with the Sponsor.
How do I modify the query below to get the list that actually has the investigator listed as well as the count?
SELECT Sponsor, COUNT(Investigator) as Investigators FROM MYCOMPLEXTABLE GROUP BY Sponsor;
Apr 07, 2021 05:31 PM|mgebhard|LINK
The syntax is as follows.
COUNT(Investigator) as Investigators,
GROUP BY Sponsor, Investigator;
19 hours, 56 minutes ago|yij sun|LINK
According to your description,I don't understand that the Investigator is the max(Investigators ) value?
Could you post your database data and structure sample?