CODEWITHSUNDEEP
mysql
Mark Harraway
Mark Harraway

Reputation: 46

First 5 rows summed per ref per type, all others equal 1, grouped by a team ID

I have an SQL statement where the first 5 rows per TYPE are selected per WSF_REF with the WEIGHTS summed together WHERE the STATUS = 'Approved'. All other rows per ref per type are classed as 1. This works fine. I'm looking to add a second group of TEAMID. 

 "SELECT WSF_REF, SUM(WEIGHT)
FROM (SELECT t1.ID, t1.WSF_REF, t1.TYPE, t1.WEIGHT
  FROM test t1
  LEFT JOIN test t2 ON t2.WSF_REF = t1.WSF_REF AND t2.TYPE = t1.TYPE AND t2.STATUS = t1.STATUS AND t2.ID < t1.ID
  WHERE t1.STATUS = 'Approved'
  GROUP BY t1.ID, t1.WSF_REF, t1.TYPE, t1.WEIGHT
  HAVING COUNT(t2.ID) < 5 
  UNION ALL
  SELECT t1.ID, t1.WSF_REF, t1.TYPE, 1
  FROM test t1
  LEFT JOIN test t2 ON t2.WSF_REF = t1.WSF_REF AND t2.TYPE = t1.TYPE AND t2.STATUS = t1.STATUS AND t2.ID < t1.ID
  WHERE t1.STATUS = 'Approved'
  GROUP BY t1.ID, t1.WSF_REF, t1.TYPE
  HAVING COUNT(t2.ID) >= 5) t
GROUP BY WSF_REF";

Example Data

CREATE TABLE test (
  `ID` INTEGER,
  `WSF_REF` INTEGER,
  `TYPE` VARCHAR(4),
  `WEIGHT` INTEGER,
  `STATUS` VARCHAR(10),
  `TEAMID` VARCHAR(10)
);



INSERT INTO test
  (`ID`, `WSF_REF`, `TYPE`, `WEIGHT`, `STATUS`, `TEAMID` )
VALUES
  ('1', '1', 'Pike', '10', 'Approved', 'Red'),
  ('2', '1', 'Pike', '10', 'Approved', 'Red'),
  ('3', '1', 'Pike', '10', 'Approved', 'Red'),
  ('4', '1', 'Pike', '10', 'Approved', 'Red'),
  ('5', '1', 'Pike', '10', 'Approved', 'Red'),
  ('6', '1', 'Pike', '10', 'Approved', 'Red'),
  ('7', '1', 'Asp', '10', 'Approved', 'Red'),
  ('8', '1', 'Asp', '10', 'Approved', 'Red'),
  ('9', '1', 'Asp', '10', 'Approved', 'Red'),
  ('10', '1', 'Asp', '10', 'Approved', 'Red'),
  ('11', '1', 'Asp', '10', 'Approved', 'Red'),
  ('12', '1', 'Asp', '10', 'Approved', 'Red'),
  ('13', '2', 'Pike', '10', 'Approved', 'Red'),
  ('14', '3', 'Pike', '10', 'Approved', 'Blue');

Intended Results

TEAM ID      WEIGHT
---------------------
Red           112
Blue           10

So the WSF_REF is still used to work the initial totals but the GROUP BY needs to be TEAMID.

Upvotes: 3

Views: 31

Answers (1)

Fahmi
Fahmi

Reputation: 37473

Try below -

DEMO

SELECT  TEAMID,SUM(WEIGHT)
FROM (SELECT t1.ID, t1.WSF_REF, t1.TYPE, t1.WEIGHT,t1.TEAMID
  FROM test t1
  LEFT JOIN test t2 ON t2.WSF_REF = t1.WSF_REF AND t2.TYPE = t1.TYPE AND t2.STATUS = t1.STATUS AND t2.ID < t1.ID
  WHERE t1.STATUS = 'Approved'
  GROUP BY t1.ID, t1.WSF_REF, t1.TYPE, t1.WEIGHT,t1.TEAMID
  HAVING COUNT(t2.ID) < 5 
  UNION ALL
  SELECT t1.ID, t1.WSF_REF, t1.TYPE, 1,t1.TEAMID
  FROM test t1
  LEFT JOIN test t2 ON t2.WSF_REF = t1.WSF_REF AND t2.TYPE = t1.TYPE AND t2.STATUS = t1.STATUS AND t2.ID < t1.ID
  WHERE t1.STATUS = 'Approved'
  GROUP BY t1.ID, t1.WSF_REF, t1.TYPE,t1.TEAMID
  HAVING COUNT(t2.ID) >= 5) t
GROUP BY TEAMID

OUTPUT:

TEAMID  weight
Red     112
Blue    10

Upvotes: 1

Related Questions