Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by Doug Hawkinson
.a{fill-rule:evenodd;}techdegree
Doug Hawkinson
Full Stack JavaScript Techdegree Student 25,073 Points

SQL Error is not making sense

I am getting the following SQL Error. The column in question is in both the Target and Source Tables:

ERROR: Error: no such column: cen.state


Query = INSERT INTO 
    cen2014DataPriStatesFinal
    (
    state, 
    year, 
    ageGrp, 
    stId, 
    stName, 
    totPop, 
    totMale, 
    totFemale, 
    waMale, 
    waFemale, 
    baMale, 
    baFemale, 
    iaMale, 
    iaFemale, 
    aaMale, 
    aaFemale, 
    naMale, 
    naFemale, 
    tomMale, 
    tomFemale, 
    wacMale, 
    wacFemale, 
    bacMale, 
    bacFemale, 
    iacMale, 
    iacFemale, 
    aacMale, 
    aacFemale, 
    nacMale, 
    nacFemale, 
    nhMale, 
    nhFemale, 
    nhwaMale, 
    nhwaFemale, 
    nhbaMale, 
    nhbaFemale, 
    nhiaMale, 
    nhiaFemale, 
    nhaaMale, 
    nhaaFemale, 
    nhnaMale, 
    nhnaFemale, 
    nhtomMale, 
    nhtomFemale, 
    nhwacMale, 
    nhwacFemale, 
    nhbacMale, 
    nhbacFemale, 
    nhiacMale, 
    nhiacFemale, 
    nhaacMale, 
    nhaacFemale, 
    nhnacMale, 
    nhnacFemale, 
    hMale, 
    hFemale, 
    hwaMale, 
    hwaFemale, 
    hbaMale, 
    hbaFemale, 
    hiaMale, 
    hiaFemale, 
    haaMale, 
    haaFemale, 
    hnaMale, 
    hnaFemale, 
    htomMale, 
    htomFemale, 
    hwacMale, 
    hwacFemale, 
    hbacMale, 
    hbacFemale, 
    hiacMale, 
    hiacFemale, 
    haacMale, 
    haacFemale, 
    hnacMale, 
    hnacFemale
    )
SELECT
    cen.state, 
    cen.year, 
    age.curCat, 
    cen.stId, 
    cen.stName, 
    sum(cen.totPop), 
    sum(cen.totMale), 
    sum(cen.totFemale), 
    sum(cen.waMale), 
    sum(cen.waFemale), 
    sum(cen.baMale), 
    sum(cen.baFemale), 
    sum(cen.iaMale), 
    sum(cen.iaFemale), 
    sum(cen.aaMale), 
    sum(cen.aaFemale), 
    sum(cen.naMale), 
    sum(cen.naFemale), 
    sum(cen.tomMale), 
    sum(cen.tomFemale), 
    sum(cen.wacMale), 
    sum(cen.wacFemale), 
    sum(cen.bacMale), 
    sum(cen.bacFemale), 
    sum(cen.iacMale), 
    sum(cen.iacFemale), 
    sum(cen.aacMale), 
    sum(cen.aacFemale), 
    sum(cen.nacMale), 
    sum(cen.nacFemale), 
    sum(cen.nhMale), 
    sum(cen.nhFemale), 
    sum(cen.nhwaMale), 
    sum(cen.nhwaFemale), 
    sum(cen.nhbaMale), 
    sum(cen.nhbaFemale), 
    sum(cen.nhiaMale), 
    sum(cen.nhiaFemale), 
    sum(cen.nhaaMale), 
    sum(cen.nhaaFemale), 
    sum(cen.nhnaMale), 
    sum(cen.nhnaFemale), 
    sum(cen.nhtomMale), 
    sum(cen.nhtomFemale), 
    sum(cen.nhwacMale), 
    sum(cen.nhwacFemale), 
    sum(cen.nhbacMale), 
    sum(cen.nhbacFemale), 
    sum(cen.nhiacMale), 
    sum(cen.nhiacFemale), 
    sum(cen.nhaacMale), 
    sum(cen.nhaacFemale), 
    sum(cen.nhnacMale), 
    sum(cen.nhnacFemale), 
    sum(cen.hMale), 
    sum(cen.hFemale), 
    sum(cen.hwaMale), 
    sum(cen.hwaFemale), 
    sum(cen.hbaMale), 
    sum(cen.hbaFemale), 
    sum(cen.hiaMale), 
    sum(cen.hiaFemale), 
    sum(cen.haaMale), 
    sum(cen.haaFemale), 
    sum(cen.hnaMale), 
    sum(cen.hnaFemale), 
    sum(cen.htomMale), 
    sum(cen.htomFemale), 
    sum(cen.hwacMale), 
    sum(cen.hwacFemale), 
    sum(cen.hbacMale), 
    sum(cen.hbacFemale), 
    sum(cen.hiacMale), 
    sum(cen.hiacFemale), 
    sum(cen.haacMale), 
    sum(cen.haacFemale), 
    sum(cen.hnacMale), 
    sum(cen.hnacFemale);
FROM
    cen2014DataPriStatesNoCounty cen
INNER JOIN
    ageBridge age
ON
    cen.ageGrp = age.priCat
GROUP BY
    age.curCat
WHERE
    cen.ageGrp != 4 AND
    cen.ageGrp != 0

2 Answers

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

I think it may be complaining that the column is missing from your GROUP BY clause. Generally, you need every non-aggregate column being selected to also be in the GROUP BY clause.

Try:

GROUP BY cen.state, cen.year, age.curCat, cen.stId, cen.stName

Also ... shouldn't the WHERE clause come before GROUP BY?

Doug Hawkinson
.a{fill-rule:evenodd;}techdegree
Doug Hawkinson
Full Stack JavaScript Techdegree Student 25,073 Points

For now I have to abandon this. I am going in a different direction but I will keep this in mind when and if I get back to it.

Doug Hawkinson
seal-mask
.a{fill-rule:evenodd;}techdegree
Doug Hawkinson
Full Stack JavaScript Techdegree Student 25,073 Points
Doug Hawkinson
.a{fill-rule:evenodd;}techdegree
Doug Hawkinson
Full Stack JavaScript Techdegree Student 25,073 Points

Steven:

I agree, that should have solved it but it didn't . And, of course, you are correct about the WHERE clause. The one exception I made after looking at it was to SELECT the max of max(cen.stId), max(cen.stName), so I don't have to GROUP BY them Technically speaking they are denormalized in this table for convenience. Nothing is summarized to them. The same is true about year.

But I do agree the changes you suggested were needed.