0

I am trying to run a query to pull the total amount of qualifying events grouped by event type and month. For example I need to know that there were 25 "LOSS_OF_COVERAGE_NON_PAYMENT" qualifying events in January, and 10 "LEAVE_OF_ABSENCE" qualifying events in January. I have tried various SQL select queries. I am not sure why the query below is returning an error that states

Invalid column name 'EventMonth'

My query:

SELECT 
    a.GroupID, c.employername, 
    MONTH(b.QualifyingEventDate) AS EventMonth, 
    b.QualifyingEventType
FROM 
    MST_MEMBER_DEMOGRAPHICS AS a 
JOIN
    DET_ENROLLMENT_DETAILS AS b ON a.BFUNIQUEID = b.BFUNIQUEID 
JOIN
    MST_EMPLOYER_GROUP_MASTER AS c ON a.groupid = c.groupid
WHERE 
    a.groupid = 'TESTClient' 
    AND QualifyingEventType <> 'NULL' 
    AND QualifyingEventDate BETWEEN '01/01/2019' AND '08/13/2019' 
GROUP BY 
    b.QualifyingEventType, EventMonth
ORDER BY 
    EventMonth, a.groupid, b.QualifyingEventType
Improve this question
3
  • Change GROUP BY b.QualifyingEventType, EventMonth to GROUP BY b.QualifyingEventType, Month(b.QualifyingEventDate) to resolve your error. Though, this probably still isn't the results you want since you aren't doing any aggregations but you state you want the "total amount" which usually implies using SUM() Commented Aug 14, 2019 at 21:01
  • 2
    You can't group by the alias, you would need to group by the derived value. Also, you can't group by only some of the non-aggregated columns. You have a few other issues here though. Your date literals are not ANSI compliant, they should be YYYYMMDD. Also, table aliases should have some context. sqlblog.org/2009/10/08/… And be careful with 'NULL'. That is a string literal. You should instead put an actual NULL in the table, not a string that says 'NULL'. Commented Aug 14, 2019 at 21:03
  • Thank you both for your responses. As you can probably tell I'ma bit lost here. Unfortunately I don't have control over the data in the DB, I just need to query it. So if I am looking to show the amount of each EventType per month how would I accomplish this? How would I incorporate SUM() into my query? I also need to create another query that does the same thing but also groups by EmployerName (removing the WHERE a.groupid = 'TESTClient' ). What would that SQL statement look like? Thank you again for the help! Commented Aug 14, 2019 at 21:20
Related questions
4343
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
2204
How to check if a column exists in a SQL Server table
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.