#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0

    Multiple Count using WHERE and HAVING


    I want to combine the following query into one query with a result in three columns.

    SELECT COUNT(*) AS MDPALNM_A_1
    FROM(SELECT DUPEDATA
    FROM CPP_WEB_MASTER
    WHERE [GROUP] = 'MDPALNM' AND (MailPiece = '1' OR MailPiece = '7') AND DATEIN >= CAST(CONVERT(varchar(50),(GETDATE() - 120), 101) AS datetime)
    GROUP BY DUPEDATA
    HAVING COUNT(RecNum) = 1) AS A1

    (SELECT COUNT(*) AS MDPALNM_A_2
    FROM(SELECT DUPEDATA
    FROM CPP_WEB_MASTER
    WHERE [GROUP] = 'MDPALNM' AND (MailPiece = '1' OR MailPiece = '7') AND DATEIN >= CAST(CONVERT(varchar(50),(GETDATE() - 120), 101) AS datetime)
    GROUP BY DUPEDATA
    HAVING COUNT(RecNum) = 2) AS A2)

    (SELECT COUNT(*) AS MDPALNM_A_3
    FROM(SELECT DUPEDATA
    FROM CPP_WEB_MASTER
    WHERE [GROUP] = 'MDPALNM' AND (MailPiece = '1' OR MailPiece = '7') AND DATEIN >= CAST(CONVERT(varchar(50),(GETDATE() - 120), 101) AS datetime)
    GROUP BY DUPEDATA
    HAVING COUNT(RecNum) = 3) AS A3)

    Can anyone help me?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select sum(case when count(recnum) = 1 then 1 else 0 end) as MDPALNM_A_1,
           sum(case when count(recnum) = 2 then 1 else 0 end) as MDPALNM_A_2,
           sum(case when count(recnum) = 3 then 1 else 0 end) as MDPALNM_A_3
      from CPP_WEB_MASTER
     where [GROUP] = 'MDPALNM'
       and MailPiece in (1,7)
       and DATEIN >= dateadd(dd,-120,GETDATE())
     group
        by DUPEDATA
    having COUNT(RecNum) <= 3
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0

    Result for Answer


    Thank you for responding...

    Here is the result from running the query suggestion from below:

    "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."




    Originally Posted by swampBoogie
    Code:
    select sum(case when count(recnum) = 1 then 1 else 0 end) as MDPALNM_A_1,
           sum(case when count(recnum) = 2 then 1 else 0 end) as MDPALNM_A_2,
           sum(case when count(recnum) = 3 then 1 else 0 end) as MDPALNM_A_3
      from CPP_WEB_MASTER
     where [GROUP] = 'MDPALNM'
       and MailPiece in (1,7)
       and DATEIN >= dateadd(dd,-120,GETDATE())
     group
        by DUPEDATA
    having COUNT(RecNum) <= 3
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Yes, my fault. Maybe better luck with this.

    Code:
    select sum(case when reccnt = 1 then 1 else 0 end) as MDPALNM_A_1,
           sum(case when reccnt = 2 then 1 else 0 end) as MDPALNM_A_2,
           sum(case when reccnt = 3 then 1 else 0 end) as MDPALNM_A_3
      from (select count(recnum) as reccnt
              from CPP_WEB_MASTER
             where [GROUP] = 'MDPALNM'
               and MailPiece in (1,7)
               and DATEIN >= dateadd(dd,-120,GETDATE())
             group 
                by DUPEDATA
            having COUNT(RecNum) <= 3) dt
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0

    Reply To Answer


    SwampBoogie,

    Thank you very much. That work great!


    Originally Posted by swampBoogie
    Yes, my fault. Maybe better luck with this.

    Code:
    select sum(case when reccnt = 1 then 1 else 0 end) as MDPALNM_A_1,
           sum(case when reccnt = 2 then 1 else 0 end) as MDPALNM_A_2,
           sum(case when reccnt = 3 then 1 else 0 end) as MDPALNM_A_3
      from (select count(recnum) as reccnt
              from CPP_WEB_MASTER
             where [GROUP] = 'MDPALNM'
               and MailPiece in (1,7)
               and DATEIN >= dateadd(dd,-120,GETDATE())
             group 
                by DUPEDATA
            having COUNT(RecNum) <= 3) dt
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0

    Question to Response


    Again, that worked great! Question for you, lets say I added to the information in the below query, How could I total the columns (like A1+B1+C1) to get total:

    select sum(case when reccnt = 1 then 1 else 0 end) as MDPAUTX_A_1,
    sum(case when reccnt = 2 then 1 else 0 end) as MDPAUTX_A_2,
    sum(case when reccnt = 3 then 1 else 0 end) as MDPAUTX_A_3
    from (select count(recnum) as reccnt
    from CPP_WEB_MASTER
    where [GROUP] = 'MDPAUTX'
    and MailPiece in (1,7)
    and DATEIN >= dateadd(dd,-120,GETDATE())
    group
    by DUPEDATA
    having COUNT(RecNum) <= 3) dt

    select sum(case when reccnt = 1 then 1 else 0 end) as MDPAUTX_B_1,
    sum(case when reccnt = 2 then 1 else 0 end) as MDPAUTX_B_2,
    sum(case when reccnt = 3 then 1 else 0 end) as MDPAUTX_B_3
    from (select count(recnum) as reccnt
    from CPP_WEB_MASTER
    where [GROUP] = 'MDPAUTX'
    and MailPiece in (2,8)
    and DATEIN >= dateadd(dd,-120,GETDATE())
    group
    by DUPEDATA
    having COUNT(RecNum) <= 3) dt

    select sum(case when reccnt = 1 then 1 else 0 end) as MDPAUTX_C_1,
    sum(case when reccnt = 2 then 1 else 0 end) as MDPAUTX_C_2,
    sum(case when reccnt = 3 then 1 else 0 end) as MDPAUTX_C_3
    from (select count(recnum) as reccnt
    from CPP_WEB_MASTER
    where [GROUP] = 'MDPAUTX'
    and MailPiece in (3,9)
    and DATEIN >= dateadd(dd,-120,GETDATE())
    group
    by DUPEDATA
    having COUNT(RecNum) <= 3) dt





    Originally Posted by swampBoogie
    Yes, my fault. Maybe better luck with this.

    Code:
    select sum(case when reccnt = 1 then 1 else 0 end) as MDPALNM_A_1,
           sum(case when reccnt = 2 then 1 else 0 end) as MDPALNM_A_2,
           sum(case when reccnt = 3 then 1 else 0 end) as MDPALNM_A_3
      from (select count(recnum) as reccnt
              from CPP_WEB_MASTER
             where [GROUP] = 'MDPALNM'
               and MailPiece in (1,7)
               and DATEIN >= dateadd(dd,-120,GETDATE())
             group 
                by DUPEDATA
            having COUNT(RecNum) <= 3) dt

IMN logo majestic logo threadwatch logo seochat tools logo