Deano Deano - 6 months ago 32
SQL Question

Group By in SQL for column

I have a query that orders by DeliveryName

SELECT ID, CreatedAt, Identifier, Name, DeliveryName, RoundedPriceSum
FROM StarOrder
WHERE
SiteID = 1 AND --StarSite.ID 1= SE, 2 = NO
ShippingTypeID = 4 -- StarShippingType.ID 4 = STORE_PICKUP SE, 10 = STORE_PICKUP NO
and CreatedAt between '2015/01/11' and '2016/05/30'
Order BY DeliveryName DESC


I am trying to group so that each Delivery Name has a total number of rows, but having no luck. Any help would be great.

Answer

You are selecting all columns including ID (having lowest granularity or detailed data) then how can you get grouped data. Also you are not using group by clause. You need to select grouping column and summary data like sum() or avg() count() etc. to achieve this.

SELECT DeliveryName, count(*)
FROM StarOrder
WHERE  SiteID = 1 
AND ShippingTypeID = 4 
and CreatedAt between '2015/01/11' and '2016/05/30'
Order BY DeliveryName DESC
group by DeliveryName 

For example if you want to group students by class you will select class and number of student. But if you selected class and name of student then you will get row for each student.

Comments