Using inner join to get column data from multiple tables

Question

I am having trouble running a query. I am trying to join 3 tables to displays the top 10 medications by the amount of times that were prescribed in a single year.

When I run it as is I get an error message about something is wrong in either the aggregate or the Select.

This is my query:

 Select Count(MED_ID), MEDICATIONS.MEDICATION_NAME, ENCOUNTER.OBSDATE 
 From MEDICATIONS
 Inner JOIN ENC_MEDICATIONS On ENC_MEDICATIONS.MED_ID = MEDICATIONS.MED_ID
 Inner JOIN ENCOUNTER On ENC_MEDICATIONS.ENC_ID = ENCOUNTER.ENC_ID
 WHERE OBSDATE Between '01/01/2011' And '12/31/2011'
 GROUP BY (MEDICATION_NAME)
 ORDER BY COUNT(MED_ID) DESC

Then this is my table model:

Where am I going wrong in the Joins to get the result I am trying to display.

Thanks! - Ann

You're missing the 3rd select/2nd non aggregated field in your group by statement. — Hatt
– Hatt, Commented May 11, 2018 at 18:12

Gordon Linoff · Accepted Answer · 2018-05-11 19:15:47Z

1

I believe you are looking for:

select Count(MED_ID), m.MEDICATION_NAME
from MEDICATIONS m Inner join
     ENC_MEDICATIONS em
     on em.MED_ID = m.MED_ID Inner JOIN
     ENCOUNTER e
     on em.ENC_ID = e.ENC_ID
where e.OBSDATE Between '2011-01-01' and '2011-12-31'
group by m.MEDICATION_NAME
order by COUNT(MED_ID) DESC 
limit 10;

Notes:

OBSDATE has no purpose in the SELECT, given what you want to do.
Date formats should use ISO/ANSI standards. YYYY-MM-DD is the most readable such format.
Use table aliases!
Qualify all column names!

answered May 11, 2018 at 19:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ann K Over a year ago

This worked thank you! I am a beginner, just learning how to write queries so thank you for all the tips!

Hatt · Accepted Answer · 2018-05-11 19:04:39Z

0

Admittedly without testing it, you need to add the group by of the second non-aggregate:

 Select MEDICATIONS.MEDICATION_NAME, ENCOUNTER.OBSDATE, Count(enc_medications.MED_ID)
     From MEDICATIONS
     Inner JOIN ENC_MEDICATIONS On ENC_MEDICATIONS.MED_ID = MEDICATIONS.MED_ID
     Inner JOIN ENCOUNTER On ENC_MEDICATIONS.ENC_ID = ENCOUNTER.ENC_ID
     WHERE OBSDATE Between '01/01/2011' And '12/31/2011'
     GROUP BY medications.MEDICATION_NAME, encounter.obsdate
     ORDER BY COUNT(enc_medications.MED_ID) DESC;

edited May 11, 2018 at 19:04

answered May 11, 2018 at 18:13

Hatt

7095 gold badges10 silver badges24 bronze badges

6 Comments

Hatt Over a year ago

Did the query end up running?

Ann K Over a year ago

Tried it out, no go, gives same type of error message.

Hatt Over a year ago

There was a comma in the answer I just took out which would throw an error - otherwise the syntax should be ok now.

Ann K Over a year ago

I saw that, I took it out and tried it again but still throwing error.

Hatt Over a year ago

Oh I think its an alias for med_id - you're counting it without an alias but it shows up in two tables you selected.

|

Collectives™ on Stack Overflow

Using inner join to get column data from multiple tables

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related