CODEWITHSUNDEEP
mysqlgroup-by
user2992507
user2992507

Reputation: 43

Group Columns if Condition is Met, Otherwise, Don't Group

So I have the following table for example

|Company Name | Weight | Date |Unique ID|
|:------------|:-------|:-----|:--------|
|ABC          |22      |10-3  |1        |
|ABC          |22      |10-3  |2        |
|DEF          |50      |10-3  |3        |
|DEF          |50      |10-3  |4        |

I need the SELECT statement to show groups only if the SUM(WEIGHT) for that group is >=100.

So the output should be:

|Company Name | Weight | Date |Unique ID|
|:------------|:-------|:-----|:--------|
|ABC          |22      |10-3  |1        |
|ABC          |22      |10-3  |2        |
|DEF          |100     |10-3  |GROUP    |

Currently, I am trying the following, but receiving an Invalid use of Group

SELECT COMPANY_NAME, DATE,
 (CASE WHEN SUM(WEIGHT) >= 100 THEN 'GROUP' ELSE UNIQUE_ID END) AS
  GROUP_CRITERIA 
  FROM TABLE GROUP BY COMPANY_NAME, DATE, 
  (CASE WHEN SUM(WEIGHT) >= 100 THEN 'GROUP' ELSE UNIQUE_ID END);

Upvotes: 1

Views: 107

Answers (3)

Sonam Gurung
Sonam Gurung

Reputation: 127

Here's a way: (Max(date) as you didn't mentioned how you were doing it for groups)

SELECT COMPANY_NAME, DATE, WEIGHT, UNIQUE_ID
FROM TABLE
WHERE COMPANY_NAME in 
  ( SELECT COMPANY_NAME FROM 
    (SELECT COMPANY_NAME, SUM(WEIGHT) FROM TABLE GROUP BY COMPANY_NAME
      HAVING SUM(WEIGHT) < 100) u)

UNION

SELECT COMPANY_NAME, MAX(DATE), SUM(WEIGHT), 'GROUP'
FROM TABLE
WHERE COMPANY_NAME not in 
  ( SELECT COMPANY_NAME FROM (SELECT COMPANY_NAME, SUM(WEIGHT) FROM TABLE GROUP BY COMPANY_NAME
    HAVING SUM(WEIGHT) < 100) u)

GROUP BY COMPANY_NAME

Upvotes: 0

CptMisery
CptMisery

Reputation: 614

I'm thinking something like this:

SELECT DISTINCT T1.company_name, IFNULL(T2.weight, T1.weight) AS weight, IFNULL(T2.date, T1.date) AS "date", IF(T2.company_name IS NULL, T1.unique_id, T2.unique_id)
FROM table T1
LEFT OUTER JOIN (
    SELECT company_name, `date`, SUM(weight) AS weight
    FROM table
    GROUP BY company_name
    HAVING SUM(weight) >= 100) T2 ON T2.company_name = T1.company_name

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562611

It appears that you mean you want groups that have sum(weight) < 100 should be included into a single group.

To do this, I'd do the first GROUP BY in a derived table subquery, then I can reference that sum in expressions of the outer query:

SELECT 
  CASE WHEN TOTAL_WEIGHT >= 100 THEN COMPANY_NAME ELSE 'GROUP' END AS COMPANY_NAME,
  DATE,
  SUM(TOTAL_WEIGHT) AS TOTAL_WEIGHT
FROM (
  SELECT COMPANY_NAME, DATE, SUM(WEIGHT) AS TOTAL_WEIGHT
  FROM MyTable
  GROUP BY COMPANY_NAME, DATE
) AS T
GROUP BY COMPANY_NAME, DATE;

Upvotes: 1

Related Questions