0

I need help creating a frequency report for data that crosses multiple columns of data. Here's an example of my data:

[Sample Data Example]

1

And here's how I need for the data to read, except that I can't figure out how to get the frequency.

[Frequency Report]

2

There may be null values in a column in a particular row, but I still need that distinct row counted. I have all of the code working except how to get the frequency/count.

This is what I've tried, but all I get are 0s for the frequency.

select distinct TEST, PANEL, UNITS, LOINC, count(distinct TEST, PANEL, UNITS, LOINC) as FREQ
from DEV_HEALTHTERM.SOURCE.LAB  
GROUP BY TEST, PANEL, UNITS, LOINC
ORDER BY FREQ DESC;

Can someone please help? Thank you. Robin

eshirvana
  • 23,227
  • 3
  • 22
  • 38
HealthcareITSQL
  • 1
  • 2

1 Answers1

0

you should count columns that are not participating in the group by .in your case you can just use * :

select TEST, PANEL, UNITS, LOINC, count(*) as FREQ
from DEV_HEALTHTERM.SOURCE.LAB  
GROUP BY TEST, PANEL, UNITS, LOINC
ORDER BY FREQ DESC;

also you don't need distinct , since you are groping by , It's redundant.

eshirvana
  • 23,227
  • 3
  • 22
  • 38