I have a table that contains details about some other partitioned tables as below:

Row    user_email      table_id           job_type
1     jack@test.com   product$20230310   Query
2     john@test.com   item$20230309      Query
3     jim@test.com    product$20230211   Query
4     jack@test.com   product$20220105   Query
5     Alex@test.com   item$20230310      Query

I would like to group by user_email and table_id but as partitions are different it will generate one row for each table/partition.

My query:

SELECT
  user_email,
  table_id,
  COUNT(*) AS total
FROM
  TABLE
GROUP BY
  user_email,
  table_id

Current results:

Row    user_email      table_id           total
1     jack@test.com   product$20230310   1
2     john@test.com   item$20230309      1
3     jim@test.com    product$20230211   1
4     jack@test.com   product$20220105   1
5     Alex@test.com   item$20230310      1

Desired output:

Row    user_email      table_id           total
1     jack@test.com   product            2
2     john@test.com   item               1
3     jim@test.com    product            1
5     Alex@test.com   item               1

>Solution :

as you onoly want the first part of the string, you can use SPLT

but better would be not to save delimitated columns, why you need the values,. as every split take time and resource that you could spent elsewhere better

SELECT
  user_email,
  SPLIT(table_id, '$')[OFFSET(0)]
,
  COUNT(*) AS total
FROM
  TABLE
GROUP BY
  user_email,
  SPLIT(table_id, '$')[OFFSET(0)]