CODEWITHSUNDEEP
sqlsql-servert-sql
Matthew Hait
Matthew Hait

Reputation: 65

MSSQL Sum of values referenced by another table

I'm attempting to create a report on the total money spent per day. In the database are these two tables. They are matched using "UID" made at creation.

I've created this query but it results in duplicate dates.

Select LEFT(f.timestamp, 10) timestamp, sum(s.Total) Total
FROM dbo.purchasing AS f
Join (SELECT uid,SUM(CONVERT(DECIMAL(18,2), (CONVERT(DECIMAL(18,4), qty) * price))) Total
        FROM dbo.purchasingitems
        GROUP BY uid)
AS s ON f.uid = s.uid
GROUP BY TIMESTAMP

purchasing:

+--+---------+------------+--------+---+
|ID|   UID   | timestamp  | contact|...|
+--+---------+------------+--------+---+
| 1|abr92nas9| 01/01/2018 | ROB    |...|
| 2|nsa93m187| 02/02/2018 | ROB    |...|
+--+---------+------------+--------+---+

purchasingitems:

+--+---------+-----+--------+---+
|ID|   UID   | QTY |  Price |...|
+--+---------+-----+--------+---+
| 1|abr92nas9|  20 | 0.2435 |...|
| 2|abr92nas9|  5  | 0.5    |...|
| 3|nsa93m187|  1  | 100    |...|
| 4|nsa93m187|  4  | 15.5   |...|
+--+---------+-----+--------+---+

Upvotes: 0

Views: 66

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Data you posted does NOT produce duplicates
No reason for the sub query 

Select LEFT(f.timestamp, 10) timestamp, 
       SUM(CONVERT(DECIMAL(18,2), (CONVERT(DECIMAL(18,4), s.qty) * s.price))) Total 
FROM dbo.purchasing AS f 
join dbo.purchasingitems s
  ON f.uid = s.uid
GROUP BY f.TIMESTAMP

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You need to group by the expression:

SELECT LEFT(f.timestamp, 10) as timestamp, sum(s.Total) as Total
FROM dbo.purchasing f JOIN
     (SELECT uid, SUM(CONVERT(DECIMAL(18,2), (CONVERT(DECIMAL(18,4), qty) * price))) as Total
      FROM dbo.purchasingitems
      GROUP BY uid
     ) s
     ON f.uid = s.uid
GROUP BY LEFT(f.timestamp, 10);

Notes:

  • You should not be storing date/time values as strings (unless you have a really good reason). If timestamp is a date, you should use cast(timestamp as date).
  • You should not be using string functions on date/times.
  • timestamp is a keyword in SQL Server (although not reserved), so it is not a good choice for a column name.
  • Your problem is that you think that GROUP BY timestamp refers to the expression in the SELECT. SQL Server does not support column aliases, so it can only refer to the column of that name.
  • I don't see a reason to convert to decimal for the multiplication. You might have a good reason.
  • You probably want order by as well, to ensure that the result set is in a sensible order.

Upvotes: 1

Related Questions