CODEWITHSUNDEEP
sqlsql-server
S2K
S2K

Reputation: 1285

Ignoring date column from Group By in Sql Server Query

I want select date column value from table but don't want group by by date. Is it possible. as i want to group the data by using other columns.

SELECT DISTINCT gin.GoodsIssueNoteNumber,gini.GoodsIssueNoteItemNumber, 
                sp.OrgPath,ISNULL(gini.IssuedQuantity,0) as 'Quantity', 
                gin.MRN, sp.SpareName,sp.SysId as 'spareid',
                stc.StoreName,sp.ItemGroupName,sp.PartCode,
                SUM(stc.StockCount) as stockCount,
                SUM(stc.TotalInventoryValue) as StockValue,
                ISNULL(gini.Cost,0) as WeightedAverageItemCost,
                ROUND (gini.IssuedQuantity * gini.Cost,2) as 'TotalIssued Cost',
                gin.Date as 'GIDATE'
FROM vwSpare sp         
INNER JOIN vwGoodsIssueNoteItem gini 
  on sp.SysId=gini.Spare 
INNER JOIN vwGoodsIssueNote gin 
  on gin.SysId in (SELECT AssociatingId FROM GoodsIssueNoteAssociation 
                   WHERE AssociatedId=gini.SysId and IsActive=1)    
LEFT JOIN vwStock stc 
  on stc.Spare=sp.SysId and stc.GoodsIssueNoteId is not null 
 and gin.StoreIDName=stc.StoreName
WHERE IsRepairable=0 and Scrap=0 and Repaired=0 and sp.IsActive=1  
  and gin.IsActive=1 and gin.Date > DATEADD(mm,-6,GETDATE()) and gini.IsActive=1 
GROUP BY sp.SpareName,stc.StoreName,sp.OrgPath,gini.IssuedQuantity,gin.MRN,
         sp.SysId,sp.ItemGroupName,sp.PartCode,gini.Cost,
         gin.GoodsIssueNoteNumber,gini.GoodsIssueNoteItemNumber,gin.Date

I don't want gin.Date in group by

Upvotes: 2

Views: 8398

Answers (3)

Sai Avinash
Sai Avinash

Reputation: 4753

The simple answer to this is that you can not do it. You can not fetch the columns that are not present in the GroupBY

Hope this helps..

Upvotes: 0

Roger Rouse
Roger Rouse

Reputation: 2335

SQL requires that all non-aggregate fields be in the GROUP BY clause. However, you can do this:

SELECT b.ungroupedColumn,  -- your ungrouped columns
       a.* -- your grouped columns
FROM (SELECT Column1, Column2, sum(Column3)
      FROM mytable
      GROUP BY Column1, Column2) a
INNER JOIN mytable b ON a.Column1 = b.Column1

The idea is that you bring back your grouped data, minus the extra column, and then join those results back to your table to add the ungrouped columns.

Here is an example, assume I have the following inventory of produce:

Type       Name       Qty
---------- ---------- -----------
Fruit      Apple      1
Fruit      Orange     2
Fruit      Banana     3
Vegetable  Carrot     4
Vegetable  Cucumber   5
Vegetable  Peas       6

I can run the following query:

SELECT b.[Name],
       a.*
FROM (SELECT [Type], sum ([Qty]) as Qty_Of_Type
      FROM Produce
      GROUP BY [Type]) a
INNER JOIN Produce b ON a.[Type] = b.[Type]

It will bring back the following results:

Name       Type       Qty_Of_Type
---------- ---------- -----------
Apple      Fruit      6
Orange     Fruit      6
Banana     Fruit      6
Carrot     Vegetable  15
Cucumber   Vegetable  15
Peas       Vegetable  15

Upvotes: 4

hashbrown
hashbrown

Reputation: 3516

As others have mentioned before, technically this can not be done. However, for practical purposes - If you don't want to use this date column in the group by clause and still want to display it in result set, one option is to use max() or min() function over your date column. In many practical purposes this will solve the problem.

But in many other cases, this will silently inject a bug in your code to be detected much later. If the date column repeats the same value across all the records of a specific group - the above solution will work perfectly. But if the dates are different across the record set under a same group, max() or min() will produce an incorrect result.

Upvotes: 0

Related Questions