Omit selected fields from the GROUP BY clause
-
Hello,
Is it possible for me to omit fields that are in the select statement from the group by clause at the bottom?I have the below SQL which will eventually be a view and the stockdateandtimein and date out values in the where clause will be passed as variables.
What I need to do is select the stock.dateandtimein and stock.dateout fields but I don't want them included in the GROUP BY clause.
Is it possible for me to do this?
SELECT
Products.REFERENCENUMBER,
Products.DESCRIPTION,
SUM(CONVERT(decimal(7, 2),
Stock.SINGLESQUANTITY) / CONVERT(decimal(7, 2), Stock.SINGLES) / CONVERT(decimal(7, 2), Stock.CARTONS)) AS PalletQty,
SUM(Stock.SINGLESQUANTITY) AS Singles,
SUM(CONVERT(decimal(7, 2), Stock.SINGLESQUANTITY) / CONVERT(decimal(7, 2), Stock.SINGLES)) AS CartonsQty,
Stock.CustomerID,
STOCK.DATEANDTIMEIN AS StockInDate,
isnull(Stock.dateout,0) AS StockOutDate,
Customer.CUSTOMERNUMBER,
Customer.Name
FROM Stock
INNER JOIN
Products ON Stock.PRODUCTID = Products.PRODUCTID
INNER JOIN
Customer ON Stock.CUSTOMERID = Customer.CUSTOMERID
WHERE Stock.StockStatus > 0 AND Stock.StockStatus < 5 AND Stock.CustomerID = 1122
AND (Stock.DateAndTimeIn <= CONVERT(DATETIME,'15/08/2018 00:00:00', 103))
AND (ISNULL(Stock.dateout,0) = 0 OR Stock.dateout > 79488)
GROUP BY Products.REFERENCENUMBER, Products.DESCRIPTION, Customer.CUSTOMERNUMBER, Stock.CustomerID, customer.name
,STOCK.DATEANDTIMEIN,Stock.dateout
ORDER BY Customer.CUSTOMERNUMBER, Products.REFERENCENUMBERThank you in advance
Paul. -
Here's a more formatted version of your query:
SELECT
Products.REFERENCENUMBER
, Products.DESCRIPTION
, PalletQty = SUM(CONVERT(DECIMAL(7, 2), Stock.SINGLESQUANTITY) / CONVERT(DECIMAL(7, 2), Stock.SINGLES) / CONVERT(DECIMAL(7, 2), Stock.CARTONS))
, Singles = SUM(Stock.SINGLESQUANTITY)
, CartonsQty = SUM(CONVERT(DECIMAL(7, 2), Stock.SINGLESQUANTITY) / CONVERT(DECIMAL(7, 2), Stock.SINGLES))
, Stock.CustomerID
, StockInDate = Stock.DATEANDTIMEIN
, StockOutDate = ISNULL(Stock.dateout, 0)
, Customer.CUSTOMERNUMBER
, Customer.Name
FROM
Stock
INNER JOIN Products ON Stock.PRODUCTID = Products.PRODUCTID
INNER JOIN Customer ON Stock.CUSTOMERID = Customer.CUSTOMERID
WHERE
Stock.StockStatus > 0
AND Stock.StockStatus < 5
AND Stock.CustomerID = 1122
AND (Stock.DateAndTimeIn <= CONVERT(DATETIME, '15/08/2018 00:00:00', 103))
AND
(
ISNULL(Stock.dateout, 0) = 0
OR Stock.dateout > 79488
)
GROUP BY
Products.REFERENCENUMBER
, Products.DESCRIPTION
, Customer.CUSTOMERNUMBER
, Stock.CustomerID
, Customer.name
, Stock.DATEANDTIMEIN
, Stock.dateout
ORDER BY
Customer.CUSTOMERNUMBER
, Products.REFERENCENUMBER;The straight answer to your question is no. The GROUP BY is performing aggregation, and the only way of moving non-aggregated columns out of the GROUP BY is by aggregating them or putting them in a separate query or subquery. But there may be an alternative which works for you. Instead of
SELECT Products.REFERENCENUMBER
Writing
SELECT REFERENCENUMBER = MAX(Products.REFERENCENUMBER)means that you can take Products.REFERENCENUMBER out of the GROUP BY, because now you are aggregating it.
What I need to do is select the stock.dateandtimein and stock.dateout fields but I don't want them included in the GROUP BY clause.
Which value would you want to see? Minimum? Maximum?The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Sorry Phil,
The fields I need to omit from the group by clause are the date fields (Stock.DATEANDTIMEIN and Stock.dateout)
Thank you
Paul. - paul 69259 - Wednesday, August 15, 2018 6:45 AM
That puts you at odds with logic. What value would you wish to output for those columns if you take them out of the grouping ? There could be any number of different values for those columns once you take them out of the group, and you have to be able to tell SQL Server which one of those values you want to see, because you want your group to be at a higher level. You can't have it both ways. The only alternative is to perhaps use windowed function aggregates, but first we need to be sure we completely understand what the needed level of grouping is and why...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Hi Steve,
The only things I want to group by are Products.REFERENCENUMBER, products.PRODUCTGROUP, Products.DESCRIPTION, Customer.CUSTOMERNUMBER, Stock.CustomerID, customer.name.
I need the dates in the query because it's a view and the dates are part of what I will be using to filter the view. It's a SQL view filtered by parameters passed via a web application.
Thank you
Paul. - paul 69259 - Wednesday, August 15, 2018 7:06 AM
I think you are not understanding what we are getting at. Here is a minimal data set:
And, in pseudo SQL, your requirement
SELECT ReferenceNumber, ProductGroup, DateOut
GROUP BY ReferenceNumber, ProductGroup
When the code executes, how would the database engine know which date to pick? Would it choose 1/1/2018 or 2/1/2018? Why?The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Well, the query is to find out what items the customer currently has in stock.
They choose a filter date and (if they wish) a product group and then the view is filtered by selecting all stock that has a booked in date of less than or equal to the customers chosen filter date and the stock that, either has no booked out date yet or, has a booked out date that is greater than the chosen filter date because technically that stock is still in stock. So in the above example, if I wanted to filter and I chose the 02/01/2018 date as a filter, neither would how up because neither booked out date is actually greater than my filter date but, if I chose 31/12/2017 then both items would show up provided they had a booked in date that was less than or equal to 31/12/2017.
-
I should add that the dates don't show up in the customers output.
They are only included in the view so that I can use them to filter it. - paul 69259 - Wednesday, August 15, 2018 7:54 AM
Both items would 'show up'? Do you mean two separate rows would be returned?
If so, having the date in the GROUP BY gives you exactly what you want. If not, please explain further.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - paul 69259 - Wednesday, August 15, 2018 8:01 AM
So the view has two different purposes?
- Passing dates to a filter
- Returning the aggregated data
Simplest solution is to create another view just for the dates and remove the dates from your existing view (except for the WHERE clause).The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - paul 69259 - Wednesday, August 15, 2018 8:01 AM
Then you CAN NOT use GROUP BY that way.... If you can only group that way, then you need a different methodology. GROUP BY would force SQL Server to want to choose a value for thee date column, and if you just try to SELECT that column, it's not going to be ABLE to know which value to pick, and will thus give you a syntax error. Maybe you need to use a CTE that does the GROUP BY that way but doesn't get the dates, but then you JOIN that CTE back to the source table to get all the dates ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
In very broad terms, you have three options for fields in the SELECT clause of a query with a GROUP BY clause.
- Group by the field
- Aggregate the field
- Skip the field
If you don't want to group by the fields, then you are left with options 2 and 3. Others have been focusing on option 2, but you might want to consider option 3.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - paul 69259 - Wednesday, August 15, 2018 8:01 AM
This is the problem. You are trying to use a view when you need either a stored procedure or an inline table-valued function. Views do not have parameters. If you want to pass parameters, then you need a procedure or a function.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Well can you just use a windowed function for the SUMS? Then filter it down to the rows you want in the reports?
- ZZartin - Wednesday, August 15, 2018 8:14 AM
Nice Idea.
I also think the same since he is using SQL SERVER 2014.Saravanan
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply