CODEWITHSUNDEEP
sqldb2jupyter
SoraHeart
SoraHeart

Reputation: 420

In DB2 SELECT MOST Frequent occurrence and link with other table

I have two table Census and Crime

From the crime table, i need to find the most frequent occurrence of community_area_number and linked the crime's community_area_number to table census's community_area_number to get the community_area_name

I am able to do the first step, but i fail at linking to another table. Please advise where have I done wrong. Thanks

%%sql
SELECT COUNT(CR.COMMUNITY_AREA_NUMBER) AS MOST_FREQ, CR.COMMUNITY_AREA_NUMBER, CE.COMMUNITY_AREA_NAME from CRIME AS CR, CENSUS AS CE 

WHERE CR.COMMUNITY_AREA_NUMBER = CE.COMMUNITY_AREA_NUMBER
GROUP BY CR.COMMUNITY_AREA_NUMBER 
ORDER BY COUNT(CR.COMMUNITY_AREA_NUMBER) DESC LIMIT 1

Expected output

MOST_FREQ ,community_area_number,, COMMUNITY_AREA_NAME

      43                25                     Uptown

Sample CENSUS

enter image description here

SAMPLE CRIME enter image description here

Upvotes: 0

Views: 203

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You should be writing the query like this:

SELECT COUNT(*) AS MOST_FREQ, 
       CR.COMMUNITY_AREA_NUMBER, CE.COMMUNITY_AREA_NAME 
FROM CRIME CR JOIN
     CENSUS CE 
     ON CR.COMMUNITY_AREA_NUMBER = CE.COMMUNITY_AREA_NUMBER
GROUP BY CR.COMMUNITY_AREA_NUMBER, CE.COMMUNITY_AREA_NAME 
ORDER BY COUNT(*) DESC
LIMIT 1;

Note the use of proper, explicit, standard, readable JOIN syntax. Never use commas in the FROM clause.

The relevant change, though, is to include CE.COMMUNITY_AREA_NAME in the GROUP BY. All non-aggregated columns should be in the GROUP BY as a general rule.

Also, COUNT(*) is simpler for counting matches, so this query uses that instead of counting the non-NULL values of a column.

Upvotes: 1

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

You are using a aggregate function COUNT(CR.COMMUNITY_AREA_NUMBER) AS MOST_FREQ and all other (non aggregate) return values need to be in the GROUP BY clause.

For your query it means try adding E.COMMUNITY_AREA_NAME to the GROUP BY.

Upvotes: 0

Related Questions