MAIN SQL QUERY

SELECT [head_id],
       [code],
       [name],
       [category_id],
       [tag_id],
       Sum(total_qty)    AS total_qty,
       Sum(total_amount) AS total_amount,
       [branch_id],
       [for_location],
       [transaction_date],
       [voided],
       [deleted_at],
       [customer_type],
       [salesman_id],
       [created_at],
       [brand_name],
       [brand_id]
FROM   (SELECT [category].[id]
                      AS
                      [category_id],
               [category].[code],
               [category].[name],
               [category].[tag_id],
               pos_sales_detail.qty * pos_sales_detail.unit_qty
                      AS total_qty,
               ( pos_sales_detail.qty * pos_sales_detail.unit_qty ) *
               pos_sales_detail.price AS
               total_amount,
               [pos_sales].[created_for]
                      AS [branch_id],
               [pos_sales].[for_location],
               [pos_sales].[transaction_date],
               [pos_sales].[voided],
               [pos_sales].[deleted_at],
               [pos_sales].[customer_type],
               [pos_sales].[salesman_id],
               [pos_sales].[created_at],
               [brand].[name]
                      AS [brand_name],
               [brand].[id]
                      AS [brand_id]
        FROM   [pos_sales]
               INNER JOIN [pos_sales_detail]
                       ON [pos_sales_detail].[transaction_id] = [pos_sales].[id]
                          AND [pos_sales_detail].[deleted_at] IS NULL
               INNER JOIN [product]
                       ON [pos_sales_detail].[product_id] = [product].[id]
               INNER JOIN [category]
                       ON [category].[id] = [product].[category_id]
                          AND [category].[deleted_at] IS NULL
               INNER JOIN [system_code] AS [brand]
                       ON [brand].[id] = [product].[brand_id]
                          AND [brand].[type_id] = 1
        WHERE  [pos_sales].[deleted_at] IS NULL
               AND [category].[id] IN ( 152, 153 )
        GROUP  BY [category].[id],
                  [category].[code],
                  [category].[name],
                  [category].[tag_id],
                  [pos_sales].[id],
                  [pos_sales].[created_for],
                  [pos_sales].[for_location],
                  [pos_sales].[transaction_date],
                  [pos_sales].[voided],
                  [pos_sales].[deleted_at],
                  [pos_sales].[customer_type],
                  [pos_sales].[salesman_id],
                  [pos_sales].[created_at],
                  [brand].[name],
                  [brand].[id],
                  [pos_sales_detail].[id],
                  [pos_sales_detail].[qty],
                  [pos_sales_detail].[unit_qty],
                  [pos_sales_detail].[price]) AS inner_query
       LEFT JOIN (SELECT Substring(tag_id, 2, ( Charindex('|', tag_id, 2) ) - 2)
                         AS
                         head_id
                  FROM   category) AS head
              ON [inner_query].[category_id] = [head].[head_id]
GROUP  BY [head_id],
          [total_qty],
          [total_amount],
          [code],
          [name],
          [tag_id],
          [category_id],
          [branch_id],
          [for_location],
          [transaction_date],
          [voided],
          [deleted_at],
          [customer_type],
          [salesman_id],
          [created_at],
          [brand_name],
          [brand_id]