SQL Server Simple SUM query not returning expected results

Question

I've searched through some other answers but haven't gotten this to work how I expect.

My query:

SELECT 
   CONVERT(VARCHAR(18),[Trx_Date],110) AS Date
  ,[TRX_TYPE]
  ,SUM([QUAN]) AS QUANt
  FROM [VM].[dbo].[INVTRX]
  WHERE SKU='7342761'  AND TRX_TYPE!='S' AND TRX_DATE BETWEEN '12-15-2016' AND '12-18-2016' AND ID_CONT=557
  GROUP BY QUAN, Trx_Type, TRX_DATE

My Results:

Date ---- TRX_TYPE ---- QUANt

12-15-2016 ---- PT ---- 48.0000

12-16-2016 ---- PT ---- 78.0000

12-16-2016 ---- PT ---- 90.0000

What I am looking for:

Date ---- TRX_TYPE ---- QUANt

12-15-2016 ---- PT ---- 48.0000

12-16-2016 ---- PT ---- 168.0000

Also, a bonus question: How do I format tables on Stackoverflow?

Thanks for any help!

Thanks, I actually just added that while I was trying various fixes. I did remove it, but the bigger issue was using the converted date as pointed out below. — escapeclaws
– escapeclaws, Commented Dec 19, 2016 at 13:46

Gurwinder Singh · Accepted Answer · 2016-12-19 13:40:15Z

1

SELECT 
   CONVERT(VARCHAR(18),[Trx_Date],110) AS Date
  ,[TRX_TYPE]
  ,SUM([QUAN]) AS QUANt
  FROM [VM].[dbo].[INVTRX]
  WHERE SKU='7342761'  AND TRX_TYPE!='S' AND TRX_DATE BETWEEN '12-15-2016' AND '12-18-2016' AND ID_CONT=557
  GROUP BY Trx_Type, Trx_Date;

answered Dec 19, 2016 at 13:40

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

escapeclaws Over a year ago

Thank you this worked perfectly. Such an easy oversight. I will accept your answer as soon as it lets me (10 minutes)

Gordon Linoff · Accepted Answer · 2016-12-19 13:45:42Z

1

You would seem to have a time component on the date. I would suggest just converting to date rather than a string:

SELECT CAST([Trx_Date] AS Date), TRX_TYPE
       SUM(QUAN) AS QUANT
FROM [VM].[dbo].[INVTRX] i
WHERE SKU = '7342761' AND
      TRX_TYPE <> 'S' AND
      TRX_DATE >= '2016-12-15' AND
      TRX_DATE < '2016-12-19' AND
      ID_CONT = 557
GROUP BY CAST([Trx_Date] AS Date), Trx_Type;

Notes:

Convert to a date to remove the time component.
You need to repeat the expression in the GROUP BY.
For date constants use ISO standard formats, such as YYYYMMDD or YYYY-MM-DD.
If your date has a time component component (which it appears to have), then don't use BETWEEN.
Only include the unaggregated columns in the GROUP BY.

answered Dec 19, 2016 at 13:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

escapeclaws Over a year ago

Thank you for the additional information, it is helpful. May I ask why one should not use between when the date has a time component? Additionally is there a preference for <> versus != ? I am clearly in the learning stage and would like to adhere to standards when possible.

Collectives™ on Stack Overflow

SQL Server Simple SUM query not returning expected results

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related