0

I am relatively new to SQL...

I am creating a summary of returned items and I would like the finished result to show the item code, the amount returned (SUM) and the reason for return. So Ideally it would be something like this:

101 - Blue Widget | 13 | Shipment Lost
101 - Blue Widget | 3  | Damaged in Transit
102 - Red Widget | 5 | Shipment Lost

So it is grouping by ITEM and RMACODE and summing the quantities

Here is a simplified version of the query I wrote for this

Select ITEM, SUM(QUANTITY), RMACODE, DATEENTERED
FROM RMAITEMS
group by ITEM, Quantity, RMACODE

I am loading this in SSRS and need DATENETERED for my report parameters to only pull records between @StartDate and @EndDate. I get en error saying DATEENTERED is invalid because it is not in the GROUP BY.

Is there a better/different way to acheive the result I am looking for?

Thanks

Andrew

I made the changes suggested by edkloczko and it appeared everything would work then, but since we removed the date from the select statement I am unable to use it in my report parameters. Here is a screenshot. I have a few ideas I will try out today but if anyone has already climbed this hill and can help me with directions I would be grateful.

Expression Needed is Absent

Amo V Elk
  • 3
  • 3
  • Does this answer your question? [MySQL - Selecting a Column not in Group By](https://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by) – diziaq May 20 '20 at 13:57
  • You need to provide sample data and desired results. An appropriate database tag (sql-server) is also warranted. – Gordon Linoff May 20 '20 at 13:59
  • diziaq, I don't think it does. – Amo V Elk May 20 '20 at 14:08
  • Gordon, Sorry, I thought the section with the 101- Blue Widget | 13 | Shipment Lost was my desired result and would imply the sample data. I will try to add more detail. – Amo V Elk May 20 '20 at 14:09
  • Why do you want to add the DATEENTERED column to your GROUP BY clause? – briskovich May 20 '20 at 14:28
  • briskovich, as far as I know, if i don't i can't use it to filter the results with parameters in srss. – Amo V Elk May 20 '20 at 14:31

1 Answers1

2

If you're looking to filter by date and don't actually need the date field...

SELECT ITEM, SUM(QUANTITY), RMACODE
FROM RMAITEMS
WHERE DATEENTERED>=STARTDATE AND DATEENTERED<=ENDDATE
GROUP BY ITEM, QUANTITY, RMACODE

This will give you all the records you need and makes the extra filtering step you're doing unnecessary - it will only select the records between the start and end dates.

I've run into the same issue before with our IBM DB2. As far as I know you need to specify ALL of the SELECT items in the GROUP BY statement. Unsure if this is specific to certain databases or not.

Ed Kloczko
  • 408
  • 4
  • 6
  • I think this worked, I am having some issues with the parameters in Visual Studio now but I once I get that figured out I will update. Thanks – Amo V Elk May 20 '20 at 15:22
  • This particular format works for the database I primarily work with because the date format is a number (20200520 for today's date), you may have to tweak it if your date is a string or datetime format. – Ed Kloczko May 20 '20 at 15:40
  • That was what I needed, It required me to change some things in the UI in Visual Studio but once I got it all figured out the report was exactly what i needed. That you so much, you gave me a better shot at having a job next week! Thanks – Amo V Elk May 20 '20 at 15:57