I'm running this query, but I'm getting and error when I add count

SELECT 
CONCAT (product_code, product_color) AS new_product_code
FROM [dbo].[Furniture]
where product = 'couch'

enter image description here

I would like to add another column and be able to count how many times a product was purchased according to its color. But I want to keep the product_code and product_color concatenated in a column. Any suggestions?

Thanks

CodePudding user response：

I suspect when you added count(), you didn't have the GROUP BY

The traditional approach

Select Prod_Color = CONCAT(product_code, product_color )
      ,count(*)
 From  [dbo].[Furniture]
 Where product = 'couch'
 Group By CONCAT(product_code, product_color )

Or using a CROSS APPLY for a single expression

Select Prod_Color 
      ,count(*)
 From [dbo].[Furniture]
 Cross Apply ( values ( CONCAT(product_code, product_color ) ) ) V(Prod_Color)
 Where product = 'couch'
 Group By Prod_Color