finding total sales and total products from 2 tables, includes a little concatenation

I have a product table, with following fields

productid
name
price
catid
deleted      (can be any one from 'y' and 'n')

and there is a sales table

salesid
productid
salestime
paymentstatus   (enum, can have any one value: CO开发者_JAVA技巧MPLETED, REMAINING, CANCELLED)

Now I need data in following format

catid         productids          totalproducts           totalsales

where

productids: this will list all the product id (comma seperated) where deleted = 'n' and belong to particular category

totalproducts: products belonging to particular category, where deleted = 'n'

totalsales: sales, where product belong to particular category and paymentstatus = 'COMPLETED'

any help.

With this query, you will get the total sales of a particlar category where the product is deleted.

Did you want this or did you want ALL the sales of a category, no matter the state of the product ?

SELECT p.catid AS catid, 
       GROUP_CONCAT(DISTINCT p.productid SEPARATOR ',') AS productids, 
       COUNT(DISTINCT p.productid) AS totalproducts, 
       COUNT(s.salesid) AS totalsales
FROM products p
    LEFT OUTER JOIN sales s
        ON s.productid = p.productid
        AND s.paymentstatus = 'COMPLETED'
WHERE p.deleted = 'n'
GROUP BY p.catid