Post reply

Invalid because it is not contained either in aggregate function

  • October 17, 2022 at 3:44 am

    #4100306

    Hi

    In below code it is giving error - column,status is invalid in the Select Statement because it is not contained either in aggregate function or Group By clause.

    Create View [dbo].[View_Summary]

    AS

    SELECT Max(S.[Name]),

    (Select count (ID) from Receipt where Id = D.Id and Status = "Receipt") as 'Receipt',

    (Select count (ID) from Issued where tId = D.Id and Status = "Issued") as 'Issued'

    FROM [Receipt] D

    JOIN [Issued] R on R.ID = D.ID

    JOIN [Master] S ON D.[ID] = S.UID

    group by D.ID

    GO

    Thanks

  • October 17, 2022 at 6:21 am

    #4100338

    your inner query has a column (status) that is not on the group by - so you need to build your query differently

    on this particular case and because you are doing a INNER join between Receipt and Issued as well as doing a GROUP BY ID (join column also used on the sub queries) you can just change

    select count(ID) ... to a sum(case when status = 'xxx' then 1 else 0 end) as xxx

     

    other cases you would need to do the group by on a inner sql and on the outer sql do the counts

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

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