SQL help, I have redundant rows and my grouping is not right

Ignition
1 
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.

2

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.

3

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

4

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

5

I don’t know what you are saying

do I add a return keyword?

6

SELECT SUM(myQuantity)

7

Then I can’t see the code though

8 
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
9

I want see like


code      qty
5            123
3            100
2            53
1            6
4            5
10

I get an error when I try that

Says myQuantity is not in my group section

11

Sorry, I edited

12

if I have quantity in the select

I get an error if it is not also in the group by

13

So you’ve got SUM(myQuantity) in the select fields and not just myQuantity?

14

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

15 
where ( t_stamp > DATEADD(minute, -50,  GETUTCDATE()))

I know I have data for this

however, it is not reading any rows in

16 
SELECT Top 40 myCode, Count (myCode) as CodeCount, SUM(myQuantity) as Qty

t_stamp might already be converted to local time

17

so I must nest my gmt differential in another DATEADD?

18 
where ( t_stamp > DATEADD(minute, -50, DATEADD(Hour,-5, GETUTCDATE())))
19

No, just use GETDATE instead of utc. I could be wrong though, check the dates you get

20

where ( t_stamp > DATEADD(minute, -50, GETDATE()))

Thank you so much