Group by with priority of column value over other row in mysql

Question

I have a database table like below.

Here I am using group by on parent_sku column. Here is my query and its result.

SELECT
  domain,
  parent,
  parent_name,
  parent_sku,
  SUM(qty)        AS sales,
  SUM(gross)      AS revenue,
  SUM(net)        AS net_revenue,
  SUM(refund_qty) AS returns,
  created_at
FROM report_byproducts_name
GROUP BY parent_sku
ORDER BY sales DESC
LIMIT 50

But I want to give priority for the domain name. Like if I give priority to themusthaves domain then it should give the result as domain = themusthaves and parent = 450212 with the same group by. Currently, I am always getting domain=tmhde and parent=325227

Expected Result

Note: Please ignore created_at column

Any suggestions will be appreciated

Thanks

Here I am adding SQL Fiddle

Answer 1

I think you need to aggregate a few more columns then join back to your table to get the relevant non aggregate columns something like

   select domain,
         parent,
         parent_name,parent_sku,
         sumsales,sumrevenue,sumnet_revenue,sumreturns
from report_byproducts_name
Join
(
SELECT
  max(parent) maxparent,
  parent_sku aggsparent_sku,
  SUM(sales)        AS sumsales,
  SUM(revenue)      AS sumrevenue,
  SUM(net_revenue)  AS sumnet_revenue,
  SUM(returns) AS sumreturns
FROM report_byproducts_name
GROUP BY parent_sku
) aggs on aggs.maxparent = parent and aggs.aggsparent_sku = parent_sku;

+--------------+--------+--------------------------------+------------+----------+-------------------+-------------------+------------+
| domain       | parent | parent_name                    | parent_sku | sumsales | sumrevenue        | sumnet_revenue    | sumreturns |
+--------------+--------+--------------------------------+------------+----------+-------------------+-------------------+------------+
| themusthaves | 450212 | Pailletten Damaged Jeans Grijs | 311        |      350 | 13692.89013671875 | 11360.31005859375 |          1 |
+--------------+--------+--------------------------------+------------+----------+-------------------+-------------------+------------+
1 row in set (0.00 sec)

Can I suggest you change float fields to decimal(10,2).

Answer 2

You can achieve this by creating temp table. Here is what you can do:

WITH temp AS
(
SELECT
  parent_sku,
  SUM(qty)        AS sales,
  SUM(gross)      AS revenue,
  SUM(net)        AS net_revenue,
  SUM(refund_qty) AS returns,
  created_at
FROM report_byproducts_name
GROUP BY parent_sku
)
SELECT * FROM report_byproducts_name
WHERE parent_sku IN (SELECT parent_sku FROM temp) AND domain = 'themusthaves'

You can give priority in Where clause.

Answer 3

Thanks for the support. I can get the expected results using the subquery like below. I don't know the way is correct or not but I am posting my answer here through which I can get expected results. Through I will wait for the better suggestions to get expected results.

SELECT 
  (SELECT domain FROM report_byproducts_name WHERE domain='themusthaves' AND parent_sku=rbn.parent_sku) as domain,
  (SELECT parent FROM report_byproducts_name WHERE domain='themusthaves' AND parent_sku=rbn.parent_sku) as parent,
  parent_name,
  parent_sku, 
  SUM(sales) as sales, 
  SUM(revenue) as revenue, 
  SUM(net_revenue) as net_revenue, 
  SUM(returns) as returns,
  created_at 
FROM report_byproducts_name rbn 
GROUP by parent_sku 
ORDER BY sales DESC 
LIMIT 50

Here is SQL Fiddle for the expected results