SQL group by date in MVC query

Question

I am trying to make a query in my database on mvc, I want to count the purchase id and group by Order date. this is my purchase model:

public class Purchase
{
    public int PurchaseID { get; set; }
    public int CustomerID { get; set; }

    public bool DeliveryChoice { get; set; }
    public int? DriverID { get; set; }

    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Order Date")]
}

and this is my query:

SELECT  
    Purchase.PurchaseID, 
    Purchase.OrderDate,
    COUNT(*) 
From Purchase 
GROUP BY Purchase.OrderDate;

this is the message i get when i execute it:

Msg 8120, Level 16, State 1, Line 1
Column 'Purchase.PurchaseID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How would I fix it to allow me to group by date (yyyy-MM-dd) and count the purchaseid

The error is telling you that `Purchase.PurchaseID` is not in the `group by` clause. — markpsmith, Oct 07 '15 at 13:39
Dupe? http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e — Andy Wiesendanger, Oct 07 '15 at 14:49

Andrey Korneyev · Accepted Answer · 2015-10-08T07:14:12.570

4

If you need to count purchaseID groupped by date, then you need query like this one:

select 
    convert(nvarchar(10), Purchase.OrderDate, 112) as OrderDate,  
    count(Purchase.PurchaseID) 
from Purchase 
group by convert(nvarchar(10), Purchase.OrderDate, 112)

Just move Purchase.PurchaseID to the argument of aggregate function count

You're getting the error you've mentioned because when you're groupping results of query - then every selected value should be either column you're groupping by, or a result of some aggregate function - this is exactly what was stated in error text.

edited Oct 08 '15 at 07:14

answered Oct 07 '15 at 13:39

Andrey Korneyev

26,353
15
70
71

how will I get the date to show in a yyyy-MM-dd format instead of '2015-09-30 13:27:24.000' ? – Roro Oct 08 '15 at 02:53
@NneneTheresaUzoegbo If you need to group only by date part (missed this point in your question on the first look) then you should strip off time part and group by this value. Say, it should be `group by convert(nvarchar(10), Purchase.OrderDate, 112)`. Showing date in this format should be done by `DisplayFormat` attribute you've already used. – Andrey Korneyev Oct 08 '15 at 07:13

SQL group by date in MVC query

1 Answers1