GROUP BY Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY Question

Author

Topic

dwdwonw
Starting Member

21 Posts

Posted - 2010-12-15 : 12:01:31

Hi. I'm working on a GROUP BY statement that only appears to work when I delete the datediff functions from my statement. Are these types of functions not allowed when a GROUP BY clause is used?

DECLARE @Calendar1 AS DateTime
DECLARE @Calendar2 AS DateTime
SET @Calendar1 = '{{{ Please choose a start date. }}}'
SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SELECT O.OrderID, C.LocalName, O.ReadyTimeFrom, DATEDIFF(n,OS.AtOrigin,OS.PickedUp) AS 'Pickup Waiting Time', O.DueTimeTo, DATEDIFF(n,OS.AtDestination,OS.Delivered) AS 'Delivered Waiting Time', DATEDIFF(n,O.DueTimeTo,OS.Delivered) AS 'Minutes Late', O.Reference1, O.Reference2, O.SpecialInst, SUM(P.Price) AS Price
FROM tblOrder AS O
INNER JOIN tblOrderStatus AS OS ON OS.OrderID = O.OrderID
inner join tblOrderItems AS P ON O.OrderID = P.OrderID
INNER JOIN tblCustomer AS C ON C.CustID = O.CustID
LEFT JOIN tblOrderDrivers AS OD ON OD.OrderID = O.OrderID
WHERE (OS.Delivered IS NOT Null OR OS.PickedUp IS NOT Null)
AND O.DueTimeTo BETWEEN @Calendar1 AND @Calendar2
GROUP BY by O.OrderID, C.LocalName, O.ReadyTimeFrom, DATEDIFF(n,OS.AtOrigin,OS.PickedUp) AS 'Pickup Waiting Time', O.DueTimeTo, DATEDIFF(n,OS.AtDestination,OS.Delivered) AS 'Delivered Waiting Time', DATEDIFF(n,O.DueTimeTo,OS.Delivered) AS 'Minutes Late', O.Reference1, O.Reference2, O.SpecialInst, P.Price

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-15 : 12:26:48

quote:
Originally posted by dwdwonw

Hi. I'm working on a GROUP BY statement that only appears to work when I delete the datediff functions from my statement. Are these types of functions not allowed when a GROUP BY clause is used?

DECLARE @Calendar1 AS DateTime
DECLARE @Calendar2 AS DateTime
SET @Calendar1 = '{{{ Please choose a start date. }}}'
SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SELECT O.OrderID, C.LocalName, O.ReadyTimeFrom, DATEDIFF(n,OS.AtOrigin,OS.PickedUp) AS 'Pickup Waiting Time', O.DueTimeTo, DATEDIFF(n,OS.AtDestination,OS.Delivered) AS 'Delivered Waiting Time', DATEDIFF(n,O.DueTimeTo,OS.Delivered) AS 'Minutes Late', O.Reference1, O.Reference2, O.SpecialInst, SUM(P.Price) AS Price
FROM tblOrder AS O
INNER JOIN tblOrderStatus AS OS ON OS.OrderID = O.OrderID
inner join tblOrderItems AS P ON O.OrderID = P.OrderID
INNER JOIN tblCustomer AS C ON C.CustID = O.CustID
LEFT JOIN tblOrderDrivers AS OD ON OD.OrderID = O.OrderID
WHERE (OS.Delivered IS NOT Null OR OS.PickedUp IS NOT Null)
AND O.DueTimeTo BETWEEN @Calendar1 AND @Calendar2
GROUP BY by O.OrderID, C.LocalName, O.ReadyTimeFrom, DATEDIFF(n,OS.AtOrigin,OS.PickedUp) ~~AS 'Pickup Waiting Time'~~, O.DueTimeTo, DATEDIFF(n,OS.AtDestination,OS.Delivered) ~~AS 'Delivered Waiting Time'~~, DATEDIFF(n,O.DueTimeTo,OS.Delivered) ~~AS 'Minutes Late'~~, O.Reference1, O.Reference2, O.SpecialInst, P.Price

dwdwonw
Starting Member

21 Posts

Posted - 2010-12-15 : 12:43:24

So then the problem is that I put the alias names in the GROUP BY list?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-15 : 13:10:09

Yes and if you really want to sum P.Price then you shouldn't group by P.Price

No, you're never too old to Yak'n'Roll if you're too young to die.

dwdwonw
Starting Member

21 Posts

Posted - 2010-12-16 : 00:08:33

Why? Is it because of indexing?

And BTW, thank you for your help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-16 : 01:19:05

A short example what the grouping is doing:
Say we have this data


Member Points
Dan    100
Fred   100
Dan    150
Fred   120

Now we want the sum of points grouped by member
SELECT Member,SUM(Points) FROM Table GROUP BY Member
This SELECT takes entries together where the values of column Member are equal.
So there is one line with value Dan and one line with value Fred and the sum of Points for each member.


Dan  250
Fred 220

But what happens if we take the column Points into the GROUP BY?
SELECT Member,SUM(Points) FROM Table GROUP BY Member, Points
This SELECT takes entries together where the values of column Member AND Points are equal!
So in our example there are all lines in the result because there are no equal lines to build groups.

Do you understand?

No, you're never too old to Yak'n'Roll if you're too young to die.

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-06 : 12:16:08

In the process of running this query (without the P.Price in the GROUP BY!), I noticed that the SUM(P.Price) is only displaying one of several columns. Is there a different function other than SUM that I should be using?

Thanks,
Dan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-07 : 05:01:06

I don't know what you mean. Can you explain by sample data and sample result and the used query please?

No, you're never too old to Yak'n'Roll if you're too young to die.

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-10 : 11:31:08

Sure.

First, the query:

DECLARE @Calendar1 AS DateTime

DECLARE @Calendar2 AS DateTime

SET @Calendar1 = '{{{ Please choose a start date. }}}'

SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SELECT O.OrderID, C.LocalName, SUM(P.Price) AS Price

FROM tblOrder AS O

INNER JOIN tblCustomer AS C ON C.CustID = O.CustID

inner join tblOrderItems AS P ON O.OrderID = P.OrderID

where O.DueTimeTo BETWEEN @Calendar1 AND @Calendar2

AND Price > 100

AND O.Status <> 4

group by O.OrderID, C.LocalName

The results:

Order ID Customer Total

666285 National Rx 400.00
666766 Test Order 441.88

The values in the related Price table are:

OrderItemI OrderID ItemID Quantity Price

3036714 666285 0 1 400
3036713 666285 3 1 0
3036712 666285 4 1 0
3036711 666285 13 1 20
3041812 666776 0 1 441.88
3041816 666776 3 1 0
3041815 666776 4 1 0
3041814 666776 13 1 29.16
3041813 666776 14 1 88.38

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:40:55

thats because of your where conditions

AND Price > 100

only first row in each group of OrderID satisfies these

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-10 : 12:07:03

So if I want to see all orders that total over $100, how would I adjust this query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 12:11:33

[code]SELECT O.OrderID, C.LocalName, SUM(P.Price) AS Price

FROM tblOrder AS O

INNER JOIN tblCustomer AS C ON C.CustID = O.CustID

inner join tblOrderItems AS P ON O.OrderID = P.OrderID

where O.DueTimeTo BETWEEN @Calendar1 AND @Calendar2

AND O.Status <> 4

group by O.OrderID, C.LocalName
HAVING SUM(P.Price) > 100
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-10 : 12:45:27

Thank you!

If I wanted to add multiple conditions (e.g. greater than 100 or less than 1) would I just change it to
HAVING SUM ((P.Price) >100 OR < 1))?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 12:50:55

HAVING SUM (P.Price) >100 OR SUM (P.Price) < 1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-12 : 11:37:14

Even when I use HAVING I still get the same results. Typically, it appears to use the last row in the tblOrderItems table. So if there are three items and the last = 0, the order displays in the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 11:46:37

sorry didnt get that. can you explain with data example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-13 : 11:08:39

Just figured out my mistake. Sorry!

I'd including the old WHERE P.Price > 100 (thought I'd deleted it). Simply adding on the HAVING conditions didn't matter since I'd already declared the conditions earlier in the statement with the WHERE.

<SET Red Face ON>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:12:07

hmm.. hope now its working fine!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-13 : 11:23:54

Perfectly! On to the next project. Selecting multiple conditions in a WHERE clause.

I used to be able to do all this, even thought in SQL when I should have been using English. The fine points seem to have leaked out of my ears over the past 15 years. Thank you for your help. I'm sure I'll be back.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:36:11

np...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwdwonw
Starting Member

21 Posts

Posted - 2011-01-13 : 13:05:58

I know this is a bit off topic but will ask here anyway. If I should start a new thread please let me know.

Is there an operator that does a NOT CONTAINS?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources