1

I'm looking to perform a sum calculation on a SQL table to find the quantity of a particular stock item held against a particular salesperson up to and including a specific date.

I'm able to perform the sum function to find the quantities on a Salesperson/item basis whenever I do not factor in the date range criteria, but as soon as i add that aspect, it all goes a bit pear shaped! Here is my code so far:

SELECT Salesperson, Item No, Sum(Quantity) AS 'Quantity'
FROM dbo
WHERE (Location Code='VAN')
GROUP BY Salesperson, Item No,
HAVING (Registering Date<={ts '2017-05-03 00:00:00'})

The location code = VAN filter is required to ensure it ignores Warehouse quantities.My SQL knowledge is limited to the few instances I run into it at work and my interaction is largely based through Microsoft Query in Excel. When looking at the above code, i figured that the 'Registering date' criteria should be in the 'WHERE' section, however when i add the criteria using the options available in Microsoft Query, it creates the 'HAVING' line.

If anyone could provide any pointers, it would be much appreciated!

Cheers

Peter

Peter Connolly
  • 121
  • 1
  • 1
  • 8
  • 1
    could you provide the exception information you are being given? – War May 05 '17 at 13:49
  • You have a comma at the end of the `GROUP BY` and `Item No` is not valid in the `GROUP BY` and probably not what you intend in the `SELECT`. – Gordon Linoff May 05 '17 at 14:15
  • War- There is no exception information or prompt displayed when i execute the query - it seems to run fine, however the quantities displayed do not match the manual sum i perform to check the calculation. Gordon Linoff - The column names used here are changed slightly due to the string length on the current data. In my DB the column for 'Item No' is Item_No – Peter Connolly May 05 '17 at 14:21

1 Answers1

1

I would imagine a query like this:

SELECT Salesperson, [Item No], Sum(Quantity) AS Quantity
--------------------^ escape the non-standard column name
FROM dbo.??
---------^ table name goes here
WHERE Location Code = 'VAN' AND
      [Registering Date] <= '2017-05-03'
------^ put the filtering condition in the correct clause
GROUP BY Salesperson, Item No
-----------------------------^ remove the comma

Your code, as written, has multiple errors. I am guessing that most are transcription errors rather than in the original query (queries don't run if no table is given in the FROM for instance). The "major" error would then be filtering in the HAVING clause rather than the WHERE clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks very much Gordon, this solved it for me. I had a suspicion it was in relation to the "HAVING" line - I've never seen that statement until now. For some reason, when applying the different criteria in Microsoft Query, it put the location code one into the WHERE statement, and the registering date criteria into the HAVING statement. – Peter Connolly May 05 '17 at 14:33
  • If i wanted to filter the resulting sum quantities, to only show instances of Salesperson/Item where the quantity is > 0, do i use the HAVING statement then? I tried a few variations, but adding in that last line brings me back to the kind of errors i had at the very start. Thanks! – Peter Connolly May 05 '17 at 14:48