# SQL help, I have redundant rows and my grouping is not right **URL:** \ **Category:** Ignition\ **Created:** [November 5, 2021, 8:18pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797 "2021-11-05T20:18:46Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:18pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/1 "2021-11-05T20:18:46Z")
```auto SELECT Top 40 myCode, Count (myCode), myQuantity FROM testmytable where (t_stamp between '2021-10-01' and '2021-11-01' or t_stamp > DATEADD(minute, -500, GETUTCDATE())) Group by myCode, myQuantity order by Sum(myQuantity)DESC; ``` I have tried many variations of this I am trying to get the descending order of quantities for each unique code. Each time there is an error code, there is quantity that shows up too of how many are junk. I want to get a descending order of these quantities. Then I can tell people hey this code is producing the most junk. My understanding is that I have to have each column name in the group section. Each select is a column in my table. The order by should be the thing that pairs all the rows together. However, I keep getting multiple rows of the same codes. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:41pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/2 "2021-11-05T20:41:14Z")
You shouldnt be grouping by quantities. What you’re saying now is, group by codes but also group the same quantities per code together as well. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:42pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/3 "2021-11-05T20:42:34Z")
if I don’t have quantities in the group section, I get an error that says I can’t have it in the select section ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:43pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/4 "2021-11-05T20:43:33Z")
Because you’re trying to return the my Quantity field without an aggregate. See your order by statement, you’re aggregating by sum. Need to do the same thing in the fields returned. Any field not within the group by needs to have an aggregate applied ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:45pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/5 "2021-11-05T20:45:25Z")
I don’t know what you are saying do I add a return keyword? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:45pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/6 "2021-11-05T20:45:55Z")
SELECT SUM(myQuantity) ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:46pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/7 "2021-11-05T20:46:44Z")
Then I can’t see the code though ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:47pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/8 "2021-11-05T20:47:35Z")
```auto SELECT Top 40 myCode, Count (myCode), SUM(myQuantity) FROM testmytable where (t_stamp between '2021-10-01' and '2021-11-01' or t_stamp > DATEADD(minute, -500, GETUTCDATE())) Group by myCode order by Sum(myQuantity) DESC ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:47pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/9 "2021-11-05T20:47:56Z")
I want see like ```auto code qty 5 123 3 100 2 53 1 6 4 5 ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:49pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/10 "2021-11-05T20:49:04Z")
I get an error when I try that Says myQuantity is not in my group section ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:49pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/11 "2021-11-05T20:49:49Z")
Sorry, I edited ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:51pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/12 "2021-11-05T20:51:05Z")
if I have quantity in the select I get an error if it is not also in the group by ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:52pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/13 "2021-11-05T20:52:52Z")
So you’ve got SUM(myQuantity) in the select fields and not just myQuantity? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:55pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/14 "2021-11-05T20:55:06Z")
okay now it is kind of working! I am missing the titles though on two columns and if I have time set to like -500 it works, but if I set it to hour, -1 then it does not work and I think it should work on -1 hour, I have a page that check the numbers are valid if I can get that ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:56pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/15 "2021-11-05T20:56:10Z")
```auto where ( t_stamp > DATEADD(minute, -50, GETUTCDATE())) ``` I know I have data for this however, it is not reading any rows in ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:56pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/16 "2021-11-05T20:56:43Z")
```auto SELECT Top 40 myCode, Count (myCode) as CodeCount, SUM(myQuantity) as Qty ``` t\_stamp might already be converted to local time ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:58pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/17 "2021-11-05T20:58:17Z")
so I must nest my gmt differential in another DATEADD? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 8:59pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/18 "2021-11-05T20:59:31Z")
```auto where ( t_stamp > DATEADD(minute, -50, DATEADD(Hour,-5, GETUTCDATE()))) ``` ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 5, 2021, 8:59pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/19 "2021-11-05T20:59:48Z")
No, just use GETDATE instead of utc. I could be wrong though, check the dates you get ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 5, 2021, 9:00pm UTC](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797/20 "2021-11-05T21:00:24Z")
`where ( t_stamp > DATEADD(minute, -50, GETDATE()))` Thank you so much [Next page](https://forum.inductiveautomation.com/t/sql-help-i-have-redundant-rows-and-my-grouping-is-not-right/52797.md?page=2)