Adding an equation when using join and sum.

Question

AUBREY MCINTYRE 40

Screenshot 2025-09-24 115559

Name from jurisdiction and reported covid cases from covid cases has to be joined. Picture is expected output.

I am only getting the names and not any data?

Erland Sommarskog 133.1K Reputation points MVP Volunteer Moderator

2025-09-24T21:17:28.7366667+00:00

Not sure I understand. In the screenshot you seem to have both names and numbers. In any case, we don't see your query, so we don't know what you are doing.
AUBREY MCINTYRE 40 Reputation points

2025-09-25T15:35:39.6733333+00:00

I am trying to estimate the number of reported cases per 1000 people in the following states. This is what I have thus far.
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2025-10-16T12:34:48.9533333+00:00

Hi @AUBREY MCINTYRE ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Erland Sommarskog and @JAMESKATTOOR-0859 for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2025-10-21T04:38:58.02+00:00

Hi @AUBREY MCINTYRE ,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks,
Anjan Kumar Chippa

Answer recommended by moderator

0 additional answers

Erland Sommarskog 133.1K Reputation points MVP Volunteer Moderator

2025-09-24T21:17:28.7366667+00:00

Not sure I understand. In the screenshot you seem to have both names and numbers. In any case, we don't see your query, so we don't know what you are doing.
AUBREY MCINTYRE 40 Reputation points

2025-09-25T15:35:39.6733333+00:00

I am trying to estimate the number of reported cases per 1000 people in the following states. This is what I have thus far.
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2025-10-16T12:34:48.9533333+00:00

Hi @AUBREY MCINTYRE ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Erland Sommarskog and @JAMESKATTOOR-0859 for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2025-10-21T04:38:58.02+00:00

Hi @AUBREY MCINTYRE ,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks,
Anjan Kumar Chippa

Answer 1

JAMESKATTOOR-0859 0

Three things:

In your WHERE clause, instead of "cc.calenderdate = 2021", you should use "YEAR(cc.calendardate) = 2021".
Change the line "SUM(cc.reportedcovidcases)..." to "SUM(cc.reportedcovidecases) * 1000.0 / j.Population". You need the population of the states in question. If the column is not "Population" change to whatever it is in the table.
Add population to the group by, i.e., group by j.name, j.Population.