Exclude a column from Group By

EPSupport2
EPSupport2 used Ask the Experts™
on
Hi All,
I want to exclude the date column from my GROUP BY statement.  I need to keep the date column as my filter applies to filtering a date range.
Is this Possible?
Any help would be appreciated.
<asp:SqlDataSource ID="calldata" runat="server" ConnectionString="<%$ ConnectionStrings:SupportConnection %>"
                SelectCommand="SELECT DISTINCT [clientName], [callType], [softwareType], [date] FROM [callData] WHERE (([clientName] LIKE '%' + @clientName + '%') AND ([softwareType] LIKE '%' + @softwareType + '%') AND ([callType] LIKE '%' + @callType + '%') AND ([date] &gt;= Convert(datetime,@startDate)) AND ([date] &lt;= Convert(datetime,@endDate))) GROUP BY [clientName], [callType], [softwareType], [date] ORDER BY [clientName] ASC" 
                FilterExpression="softwareType LIKE '{0}%' AND callType LIKE '%{1}%' AND clientName LIKE '%{2}% AND ('{3}' = '' OR date >= '{3}') AND ('{4}' = '' OR date <= '{4}')"                
                UpdateCommand="UPDATE [callData] SET [clientName] = @clientName, [callType] = @callType, [softwareType] = @softwareType, [date] = @date"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
myrotarycar

Commented:
If you still need the date to show up on your results, you will have to aggregate it in order to be able to exclude it from the GROUP BY clause. Either MIN() MAX().
HainKurtSr. System Analyst

Commented:
you can use either of these
SELECT DISTINCT [clientName], [callType], [softwareType]
  FROM [callData] 
 WHERE (([clientName] LIKE '%' + @clientName + '%') 
   AND ([softwareType] LIKE '%' + @softwareType + '%') 
   AND ([callType] LIKE '%' + @callType + '%') 
   AND ([date] &gt;= Convert(datetime,@startDate)) 
   AND ([date] &lt;= Convert(datetime,@endDate))) 
 GROUP BY [clientName], [callType], [softwareType]
 ORDER BY [clientName] ASC

or as previously stated

SELECT DISTINCT [clientName], [callType], [softwareType], max([date]) max_date, min([date]) min_date 
  FROM [callData] 
 WHERE (([clientName] LIKE '%' + @clientName + '%') 
   AND ([softwareType] LIKE '%' + @softwareType + '%') 
   AND ([callType] LIKE '%' + @callType + '%') 
   AND ([date] &gt;= Convert(datetime,@startDate)) 
   AND ([date] &lt;= Convert(datetime,@endDate))) 
 GROUP BY [clientName], [callType], [softwareType], [date] 
 ORDER BY [clientName] ASC

Open in new window

EPSupport2

Author

Commented:
I actually don't need the date column to show in my results, I just need to be able to filter the date to narrow the results.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Read More
myrotarycar

Commented:
Use HainKurt's firsth code-snippet suggestion.

SELECT DISTINCT [clientName], [callType], [softwareType] 
  FROM [callData]  
 WHERE (([clientName] LIKE '%' + @clientName + '%')  
   AND ([softwareType] LIKE '%' + @softwareType + '%')  
   AND ([callType] LIKE '%' + @callType + '%')  
   AND ([date] &gt;= Convert(datetime,@startDate))  
   AND ([date] &lt;= Convert(datetime,@endDate)))  
 GROUP BY [clientName], [callType], [softwareType] 
 ORDER BY [clientName] ASC

Open in new window

EPSupport2

Author

Commented:
i get the following error after I try that.
DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'date'.
HainKurtSr. System Analyst

Commented:
remove date column from your code... you are trying to use it...
Sr. System Analyst
Commented:
or from aspx page, look for that column, and remove it...
EPSupport2

Author

Commented:
I need to be able to filter the date range from the data, but I don't need it to show in the gridview.
If I remove the column, will I still beable to filter the data?
myrotarycar

Commented:
Yes. Change how you filter from within the GridView connection manager. And make sure you refresh the grid fields.
EPSupport2

Author

Commented:
Excellent. Thanks again.. I had to get around the tree in front of me :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial