How can i select combine category name in my SQL select statement?

Question

I have a select statement as below:

SELECT 
  sales.Added_by as "Added_By",
  CONCAT(HOUR(sales.Sales_Date), ':00-', HOUR(sales.Sales_Date)+2, ':00') AS Hours,
  COUNT(sales.Sales_ID) AS `Sales_ID`,
  sum(sales.Quantity) as "Quantity",
  sum(sales.Tax_Amount) as "Tax_Amount",
  sum(sales.Discount_Amount) as "Discount_Amount",
  sum(sales.Total_Price) as "Total_Price",
  sum(sales.Cash) as "Cash",
  sum(sales.Change) as "Change",
  MAX(sales_detail.Category_ID) as "Category_ID",
  MAX(sales_detail.Stock_Name) as "Stock_Name",
  categories.Category_Name
FROM sales
  inner join sales_detail on sales.Sales_Number=sales_detail.Sales_Number
  inner join categories on categories.Category_ID = sales_detail.Category_ID
GROUP BY HOUR(sales.Sales_Date) asc, sales.Added_By asc 

My question is how can I add the name of the category in

MAX(sales_detail.Category_ID) as "Category_ID"

column? Output should be like 1 - Category A.

Your help is much appreciated.

Answer 1

A hackish way for mysql specific use GROUP_CONCAT to get comma separated list of categories for each group ordered by category id in descending order and then use SUBSTRING_INDEX string function to pick the first value

SUBSTRING_INDEX(
     GROUP_CONCAT(categories.name ORDER BY categories.Category_ID DESC)
,',',1) "Category_Name",

But how can i add in into category _id column together

CONCAT( MAX(sales_detail.Category_ID),
        '-', 
       SUBSTRING_INDEX(
          GROUP_CONCAT(categories.name ORDER BY categories.Category_ID DESC)
       ,',',1)
) AS "Category"