Post reply

Cannot perform an aggregate function

  • March 7, 2017 at 7:21 pm

    #341464

    Cannot perform an aggregate function problem | The ASP.NET Forums        I need to count how many times a customer has had a confirmed shipment sit on the dock in the past working week (the result would be an integer). Confirmed in the codes table is the date it gets tagged:

    • TABLE dbo.orders --o ( OrderID (pk, int, NOT NULL),
      CustomerID varchar(5) NOT NULL,
      trackid (varchar(50) NOT NULL),
      Status varchar(50) null,
      Ordershipped datetime NULL,
      Orderarrived datetime NULL
      CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC) );

      TABLE dbo.codes --cod
      (  codeID (pk uniqueidentifier NOT NULL),
       trackid (varchar(50) NOT NULL),  
      confirmed datetime NULL,  
      code (varchar(2) NULL),  
      CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC) );

      select cu.customerid, ,[CONFIRMED_LAST_WK] =  sum(case when (select convert(date,cod.confirmed) from Codes cod where o.trackid = cod.trackid and cod.confirmed <>'') >= DATEADD(DAY, -8 + CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN 1 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END, GETDATE()) then 1 else 0 end) from orders o group by customerid

      But this is getting "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." error, i cant find a good way to subquery this
      ??? Thanks in advance

  • March 7, 2017 at 7:51 pm

    #1932200

    Post DDL statement and sample data insert statements to ensure get responses.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • March 7, 2017 at 11:17 pm

    #1932213

    You can try something like this.  NOTE: The lack of data or expected results means that this code is untested.
    WITH cteDateCalc AS (
      SELECT
       trackid
      , confirmed
      , CalcDate = DATEADD(DAY, -8 + CASE DATEDIFF(DAY, 0, GETDATE()) % 7
                 WHEN 0 THEN 1
                 WHEN 5 THEN 2
                 WHEN 6 THEN 1
                 ELSE 0
                END, GETDATE())
      FROM #Codes
      WHERE confirmed IS NOT NULL
    )
    SELECT
      o.CustomerID
    , CONFIRMED_LAST_WK = SUM(CASE WHEN cte.confirmed >= cte.CalcDate THEN 1
               ELSE 0
              END)
    FROM #orders AS o
    INNER JOIN cteDateCalc AS cte
     ON o.trackid = cte.trackid
    GROUP BY o.CustomerID

    How to post data/code on a forum to get the best help.

    • Viewing 3 posts - 1 through 2 (of 2 total)

    You must be logged in to reply to this topic. Login to reply