CODEWITHSUNDEEP
sqlpostgresqlgroup-byviewpsql
Anna Goldberg
Anna Goldberg

Reputation: 113

Creating views with group by not grouping properly

I have data that looks like Music92838, Entertainment298928, SPORTS2837 etc. in my Event_type column, and I'm trying to create a view that groups the number of performances by event_type

I tried to do

CREATE VIEW Performances_Type_Cnt
 AS SELECT regexp_replace(E.event_type, '[^a-zA-Z]', '', 'g') AS Event_Type,
  COUNT(*)
 FROM Event_Type E, Performance P
WHERE E.event_id = P.event_id 
GROUP BY Event_Type;

Using regex to only select characters, and then group by Music, Sports etc in the Group By Event_type. But something isn't working as in my results I'm getting

  event_type   | count
---------------+-------
 MUSIC         |     1
 SPORTS        |     5
 MUSIC         |     8
 MUSIC         |     3

where Music appears more than once in event_type, which isn't the correct result.

Any and all help appreciated!

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

The "problem" is that Postgres allows column aliases in the GROUP BY. So, it is confused as to whether the EVENT_TYPE comes from the table or the alias. One simple solution is to use positional notation:

CREATE VIEW Performances_Type_Cnt AS
    SELECT regexp_replace(E.event_type, '[^a-zA-Z]', '', 'g') AS Event_Type,
           COUNT(*) as cnt
    FROM Event_Type E JOIN
         Performance P
         ON E.event_id = P.event_id 
    GROUP BY 1;

I made some other changes:

  • Replaced the implicit join with an explicit JOIN. This is the standard way to write joins in SQL.
  • Added a column alias for COUNT(*).

Upvotes: 3

Related Questions