February 2, 2023

SharewarePile.com – Latest Web Hosting News

Latest Web Development and Hosting News

How to ensure distinct count value return based on larger variable set

Im still a SQL noob but have been trying to improve my skills through playing with data at my job. I’m trying to return a simple count of distinct clients in certain age groups recorded during a certain timeframe with a certain BMI. I’m getting stuck on what syntax to use to make sure that if the same client has had multiple BMI values stored in the table during that period, it is only taking the top result from the table and counting it in that “bucket” and not duplicating any counts.

BMI ranges are, > 18.5, between 18.5-29.9, between and > 29.9

How would I combine these 3 statements below to make sure only the top 1 result from the table gets counted (so if client came in for BMI reading in both January and March, it would only count the record from March and count it with the appropriate BMI range). I’ve used the sum (case when) function before, but not with this many parameters and everything I’ve tried has failed to execute. Any help is greatly appreciated!

Select count (distinct client) from table1 where daterecorded between ‘2021-01-01’ and ‘2021-12-31’ and DOB between ‘2017-01-01’ and ‘2021-12-31’ and ‘BMI > ‘18.5’

Select count (distinct client) from table1 where daterecorded between ‘2021-01-01’ and ‘2021-12-31’ and DOB between ‘2017-01-01’ and ‘2021-12-31’ and BMI between ‘18.5’ and ‘29.9’

Select count (distinct client) from table1 where daterecorded between ‘2021-01-01’ and ‘2021-12-31’ and DOB between ‘2017-01-01’ and ‘2021-12-31’ and BMI > ‘29.9’

submitted by /u/Alaric_Darconville
[link] [comments]


Go to Source of this post
Author Of this post: /u/Alaric_Darconville
Title Of post: How to ensure distinct count value return based on larger variable set
Author Link: {authorlink}