CODEWITHSUNDEEP
sql-server
Kewin Björk Nielsen
Kewin Björk Nielsen

Reputation: 679

how to use a datediff together with a count having statement

I am trying to get the time it takes between 2 items before they are sold if that specific item had been sold more than 2 times and then how would I then be able to use the alias soldtime later for orderby

but I get this error:

Column 'sales.InvoiceDetailItem.DateTimeCreated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8120, Level 16, State 1, Line 4 Column 'file.Item.DateTimeCreated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    SELECT
  DATEDIFF(DAY,PS.[DateTimeCreated],P.[DateTimeCreated]) AS 'SoldTime'
, p.[Name]
, PS.[ItemCode]
, COUNT(*)
FROM
[ISTABLocalDB].[file].[Item] P
inner join [sales].[InvoiceDetailItem] PS on P.[LastInvoice_ID] = PS.Invoice_ID  
GROUP BY
    p.[Name], PS.[ItemCode]
HAVING 
    COUNT(*) >= 2
    order by p.[Name]

Upvotes: 1

Views: 487

Answers (2)

Ali Mirzaie
Ali Mirzaie

Reputation: 99

you can select count( PS.[ItemCode]) why you are using '*' ?

SELECT
  DATEDIFF(DAY,PS.[DateTimeCreated],P.[DateTimeCreated]) AS 'SoldTime'
, p.[Name]
, PS.[ItemCode]
, count( PS.[ItemCode]) 
FROM
[ISTABLocalDB].[file].[Item] P
inner join [sales].[InvoiceDetailItem] PS on P.[LastInvoice_ID] = PS.Invoice_ID  
GROUP BY
    p.[Name], PS.[ItemCode]
HAVING 
    count( PS.[ItemCode])  >= 2
    order by p.[Name]

Upvotes: 1

nvogel
nvogel

Reputation: 25534

I guess you want something like this:

SELECT
  DATEDIFF(DAY,MIN(PS.[DateTimeCreated]),MAX(P.[DateTimeCreated])) AS 'SoldTime'
, p.[Name]
, PS.[ItemCode]
, COUNT(*)
FROM
[ISTABLocalDB].[file].[Item] P
inner join [sales].[InvoiceDetailItem] PS on P.[LastInvoice_ID] = PS.Invoice_ID  
GROUP BY
    p.[Name], PS.[ItemCode]
HAVING 
    COUNT(*) >= 2
    order by p.[Name];

Upvotes: 1

Related Questions