I am running a query that returns:

Date       Set   Data
2021-07-02  1      A
2021-07-02  2      B
2021-07-02  3      C
2021-08-15  1      D
2021-10-27  1      E
2021-10-27  2      F

I need to also return the number of Sets for each date:

Date       Set   Data NoSets
2021-07-02  1      A     3
2021-07-02  2      B     3
2021-07-02  3      C     3
2021-08-15  1      D     1
2021-10-27  1      E     2
2021-10-27  2      F     2

SELECT csm_pat_exe_date.pedate_id, csm_patient_exercise.pat_exe_id,  
                 csm_exercise_details.ede_id, csm_exercise.exe_id, 
ses_pat_id, ses_pat_note, ses_pat_body_weight, ses_pat_blood_pressure, ses_pat_blood_glucose_level,
exe_name, ede_order, ede_type,  
                 ede_unit, weekofyear(csm_pat_exe_date.pedate_date) AS weekNumber, csm_pat_exe_date.pedate_date, peds_id, peds_set, peds_result, pedate_note, t2.noSets  
                 FROM csm_session_patient, csm_session, csm_exercise, csm_patient_exercise, csm_exercise_details,  
                     csm_pat_exe_date_set, csm_pat_exe_date 
INNER JOIN ( 
                     SELECT pedate_date, COUNT(*) as noSets 
                         FROM csm_patient_exercise, csm_pat_exe_date
                    WHERE  csm_patient_exercise.pat_id = '1'
                          AND csm_patient_exercise.pat_exe_id = csm_pat_exe_date.pat_exe_id
                     GROUP BY pedate_date 
                     ) t2 ON csm_pat_exe_date.pedate_date = t2.pedate_date
 
                 WHERE csm_session_patient.pat_id= '1'
AND csm_session_patient.ses_id = csm_session.ses_id 
                     AND csm_session.ses_date = csm_pat_exe_date.pedate_date
AND exe_archived IS NULL  
                     AND csm_exercise.exe_id = csm_patient_exercise.exe_id   
                     AND csm_patient_exercise.pat_exe_id = '1'   
                     AND csm_patient_exercise.exe_id = csm_patient_exercise.pat_exe_id  
                     AND csm_patient_exercise.pat_exe_id = csm_pat_exe_date.pat_exe_id 
AND csm_pat_exe_date.pedate_date >= '2021-06-01'  
                     AND csm_pat_exe_date.pedate_date <= '2021-09-24'  
                     AND csm_pat_exe_date.pedate_id = csm_pat_exe_date_set.pedate_id 
 
                     AND csm_pat_exe_date_set.ede_id = csm_exercise_details.ede_id  
                 ORDER BY csm_pat_exe_date.pedate_date, peds_set, ede_order;

CodePudding user response：

You may add the count window function to your select clause eg

Mysql 8

    SELECT
        `Date`,
        `Set`,
        `Data`,
        `NoSets`,
        COUNT(*) OVER (PARTITION BY Date) as NoSets
    FROM
      ...include the rest of your query here

Older Mysql Versions You may use variables or aggregates to achieve your count

Schema (MySQL v5.5)

CREATE TABLE my_table (
  `Date` DATETIME,
  `Set` INTEGER,
  `Data` VARCHAR(1)
);

INSERT INTO my_table
  (`Date`, `Set`, `Data`)
VALUES
  ('2021-07-02', '1', 'A'),
  ('2021-07-02', '2', 'B'),
  ('2021-07-02', '3', 'C'),
  ('2021-08-15', '1', 'D'),
  ('2021-10-27', '1', 'E'),
  ('2021-10-27', '2', 'F');

Query #1

SELECT
    `Date`,
    `Set`,
    `Data`,
    `NoSets`
FROM (
    SELECT 
        t.*, 
        @maxcnt:=IF(@prevdate2=`DATE`,IF(@maxcnt>cnt,@maxcnt,cnt),cnt) as NoSets,
        @prevdate2:=`Date`
    FROM (
        SELECT
            @cnt:=IF(@prevdate1=`DATE`,@cnt 1,1) as cnt,
            @prevdate1:=`Date`,
            m.*
        FROM 
            my_table m
        CROSS JOIN (SELECT @cnt:=0,@prevdate1:=NULL) vars
        ORDER BY `Date`
    ) t 
    CROSS JOIN (SELECT @maxcnt:=0,@prevdate2:=NULL) vars
    ORDER BY `Date`,cnt DESC
) t2;

Query #2

SELECT
    t1.`Date`,
    t1.`Set`,
    t1.`Data`,
    t2.`NoSets`
FROM
    my_table t1
INNER JOIN (
    SELECT `Date`, COUNT(*) as NoSets
    FROM my_table
    GROUP BY `Date`
) t2 ON t1.`Date`=t2.`Date`;

or

SELECT
    t1.`Date`,
    t1.`Set`,
    t1.`Data`,
    (
      SELECT COUNT(*) FROM my_table t2 WHERE t2.Date=t1.Date
    ) as `NoSets`
FROM
    my_table t1

View on DB Fiddle

Let me know if this works for you.