SQL Error “is not in the GROUP BY list”

101
March 07, 2018, at 10:10 AM

I'm trying to do a relatively complicated query that shows me what tags are used in conjunction with tag A, and it works on sqlfiddle but not on Bigquery. Here is my code:

select `tag_touched`, count(*) / numsessions as ratio
from (select s.`session_id`, `tag_touched`, max(created) as 
maxcreated, a.maxcreated_a, ss.numsessions
  from [TABLENAME] s join
       (select s.`session_id`, max(s.Created) as maxcreated_a
        from [TABLENAME] s
        where `tag_touched` = 'A'
        group by s.`session_id`,
   ) a
   on s.`session_id` = a.`session_id` cross join
   (select count(distinct `session_id`) as numsessions
    from [TABLENAME]
    where `tag_touched` = 'A'
   ) ss
  group by s.`session_id`, s.`tag_touched`, a.maxcreated_a, ss.numsessions
  having max(created) > maxcreated_a) s
group by `tag_touched`;

The error is:

 Error: Expression '`tag_touched`' is not present in the GROUP BY list

Any ideas on fixing this? Thanks

Answer 1

You have some minor errors in the syntax. Perhaps this will fix the problem:

select `tag_touched`, count(*) / numsessions as ratio
from (select s.`session_id`, s.`tag_touched`,
             max(s.created) as maxcreated, a.maxcreated_a, ss.numsessions
      from [TABLENAME] s join
           (select s.`session_id`, max(s.Created) as maxcreated_a
            from [TABLENAME] s
            where `tag_touched` = 'A'
            group by s.`session_id`
           ) a
           on s.`session_id` = a.`session_id` cross join
           (select count(distinct `session_id`) as numsessions
            from [TABLENAME]
            where `tag_touched` = 'A'
           ) ss
      group by s.`session_id`, s.`tag_touched`, a.maxcreated_a, ss.numsessions
      having max(s.created) > maxcreated_a
     ) s
group by `tag_touched`, numsessions;
READ ALSO
Conditional aggregation in mysql

Conditional aggregation in mysql

I am trying to calculate the conversion rate (defined as number of purchases divided by the number of product views) for items sold at different price points but then only report on the conversion rate for the sales price set for tomorrowHowever, there is another...

112
Connecting to database on Android using room

Connecting to database on Android using room

I have been looking into databases and the persistence library Room for allowing live updates to my android application but I am struggling to grasp how Room really communicates to databasesI am not sure I have the right idea of how it is meant to be used

87
Loading database values in function and html table

Loading database values in function and html table

I've created two functions in php, one of which loads data from a MySQL table and creates an array and the other creates an HTML table view from that data

93