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.

M Khalid Junaid · Accepted Answer · 2017-11-08T13:00:45.830

0

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"

edited Nov 08 '17 at 13:00

answered Nov 08 '17 at 05:37

M Khalid Junaid

63,861
10
90
118

Hi Khalid, this select statement is correct. But how can i add in into category _id column together? – Test 87 Nov 08 '17 at 08:22
@Test87 you mean id-categoryname ? something like this 12-foods ? – M Khalid Junaid Nov 08 '17 at 08:45
@Khalid, thanks a lot for your answer, is exactly what i want. awesome! – Test 87 Nov 11 '17 at 12:22

score 0 · Answer 2 · answered Nov 08 '17 at 06:32

If your category name is always the same for all rows which are grouped together, you can just add categories.Category_Name to the GROUP BY clause, and concat the column name as usual:

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) || ' - ' || categories.Category_Name 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, categories.Category_Name

Hi macmoonshine, the category will be different. – Test 87 Nov 08 '17 at 08:21 — Test 87, Nov 08 '17 at 08:21

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

2 Answers2