CODEWITHSUNDEEP
sql-servergroup-byaggregate-functions
user1551056
user1551056

Reputation: 27

Why Group By clause is not working?

Below is the code. I need the result date wise.

    Declare @tempInvoiceStatus TABLE
    (
        [InvNum] [varchar](25) NOT NULL,
        [ExportStatus] [int] NOT NULL,
        [ExportFailReason] [varchar](max) NULL,
        [ImportStatus] [int] NULL,
        [ImportFailReason] [varchar](max) NULL,
        [ExportDateTime] [datetime] NULL,
        [InvoiceType] [varchar](50) NOT NULL,
        [ExportType] [varchar](50) NOT NULL
    );

    Insert @tempInvoiceStatus
    select * from InvoiceStatus  where CONVERT(VARCHAR(10),ExportDateTime,10)  between CONVERT(VARCHAR(10),@StartDate,10) and CONVERT(VARCHAR(10), @EndDate,10)


    select CONVERT(VARCHAR(10),ExportDateTime,10)as ExportDate, COUNT(*) as Total_Records,
    (select COUNT(ExportStatus) from @tempInvoiceStatus I2 where I2.ExportDateTime=I1.ExportDateTime 
    and ExportStatus=1)as Success,
    (select COUNT(ExportStatus) from @tempInvoiceStatus I3 where I3.ExportDateTime=I1.ExportDateTime 
    and ExportStatus=2)as Failed
    from @tempInvoiceStatus I1 group by (Cast(ExportDateTime as DATE))order by ExportDateTime

I need the result date wise. Why am I getting the following error?

Column 'InvoiceStatus.ExportDateTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 1063

Answers (4)

Szymon
Szymon

Reputation: 43023

You have to use the same element in SELECT and ORDER BY and in inner queries as in GROUP BY:

select CONVERT(VARCHAR(10),Cast(ExportDateTime as DATE),10)as ExportDate, COUNT(*) as Total_Records,
(select COUNT(ExportStatus) from @tempInvoiceStatus I2 where Cast(I2.ExportDateTime as date)=Cast(I1.ExportDateTime as DATE)
and ExportStatus=1)as Success,
(select COUNT(ExportStatus) from @tempInvoiceStatus I3 where cast(I3.ExportDateTime as date)=Cast(I1.ExportDateTime as DATE)
and ExportStatus=2)as Failed
from @tempInvoiceStatus I1 group by (Cast(ExportDateTime as DATE)) 
order by (Cast(ExportDateTime as DATE))

Upvotes: 1

somesh
somesh

Reputation: 3578

select CONVERT(VARCHAR(10),ExportDateTime,10)as ExportDate, COUNT(*) as Total_Records,
    (select COUNT(ExportStatus) from @tempInvoiceStatus I2 where I2.ExportDateTime=I1.ExportDateTime 
    and ExportStatus=1)as Success,
    (select COUNT(ExportStatus) from @tempInvoiceStatus I3 where I3.ExportDateTime=I1.ExportDateTime 
    and ExportStatus=2)as Failed
    from @tempInvoiceStatus I1 group by **ExportDateTime** order by ExportDateTime

Upvotes: 0

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

You have to include the column you want to specify in SELECT statement to have it in the GROUP..BY clause. So add the column InvoiceStatus.ExportDateTime to your GROUP..BY clause.

Upvotes: 0

Tilak
Tilak

Reputation: 30698

Add InvoiceStatus.ExportDateTime to group by clause. The Columns should be same in Select and Group By. as You are using ExportDateTime and not (Cast(ExportDateTime as DATE)), you are getting the error.

 select CONVERT(VARCHAR(10),ExportDateTime,10)as ExportDate, COUNT(*) as Total_Records,
    (select COUNT(ExportStatus) from @tempInvoiceStatus I2 where I2.ExportDateTime=I1.ExportDateTime 
    and ExportStatus=1)as Success,
    (select COUNT(ExportStatus) from @tempInvoiceStatus I3 where I3.ExportDateTime=I1.ExportDateTime 
    and ExportStatus=2)as Failed
    from @tempInvoiceStatus I1 group by ExportDateTime)) order by ExportDateTime

Upvotes: 0

Related Questions