Error converting data type nvarchar to bigint in SQL Server in select statement

Question

I have 2 tables, one stores the points of users while the other one stores the products that have been purchased by the users.

I have been trying to find the net points that a user has. So while deducting one from the other I have tried these two queries both seems to have a problem.

I have written a SQL query to find difference but there is an error regarding type casting

select sum(point)
from UserPoints
where Userid = (select id
                from AspNEtusers
                where username ='1-V2-4128')
-
(
    select sum(o.quantity*o.pointperitem) as Total
    from AspNetusers as A
    inner join
    MyOrders as M
    inner join OrderedProducts as O
        on O.Orderid = M.id
        on M.Userid= A.id
    where A.username='1-V2-4128'
)

Error thrown:

Error converting data type nvarchar to bigint

Then I tried

select 
    sum(point) 
from 
    UserPoints 
where 
    Userid = (select id 
              from AspNEtusers 
              where username ='1-V2-4128')
- convert(
bigint(50),
(
select sum(o.quantity*o.pointperitem) as Total from AspNetusers as A inner join MyOrders as M inner join OrderedProducts as O
on O.Orderid = M.id
on M.Userid= A.id
where A.username='1-V2-4128')
)

Error thrown is

CAST or CONVERT: invalid attributes specified for type 'bigint'

Answer 1

I found weak knowledge of T-SQL after studying this query.

It is clear from the query that you are willing to subtract the total points from UserPoint with total points per item from OrderedProducts.

But the problem is:

SELECT PointTotal - SELECT PointPerItemTotal  -- Which is not the way to subtract in T-SQL

It should be:

SELECT (PointTotal - PointPerItemTotal) AS Total -- Correct way for any
                                                 -- arithmetic operation in T-SQL

So, the query should be:

DECLARE @UserId NVARCHAR(50) = (SELECT Id -- supposing uniqueidentifier
                                FROM AspNEtusers 
                                WHERE UserName = '1-V2-4128')

SELECT 
    (
        ISNULL((
            SELECT  
                SUM(Point) AS TotalUserPoints -- column should be numberic type     
            FROM  
                UserPoints
            WHERE  
                Userid = @UserId
        ), 0)
        - ISNULL((
            SELECT   
                SUM(o.quantity*o.pointperitem) AS TotalPointPerItem -- column should be numberic type
            FROM     
                MyOrders AS M
                INNER JOIN OrderedProducts AS O
                    ON O.Orderid = M.id         
            WHERE    
                M.Userid = @UserId
        ), 0)
    ) AS Total

Answer 2

As I don't have points to comment putting this as answer. For the second query the issue is with bigint(50). You should simply use convert(bigint,expression).

For the first query I am guessing you are trying to save the data from the calculation to a nvarchar may be. Or else check the data of the columns you use for the calculation as they might contain string values. Or in the where clause for all those columns check IsNumeric.

i.e.

select  sum(point) 
from    UserPoints 
where   Userid = (  select  id 
                    from    AspNEtusers 
                    where   username ='1-V2-4128') 
        AND ISNUMERIC(point) = 1

- 

(   select  sum(o.quantity*o.pointperitem) as Total 
    from    AspNetusers as A 
    inner join MyOrders as M 
        on O.Orderid = M.id
    inner join OrderedProducts as O  
        on M.Userid= A.id 
    where A.username='1-V2-4128' AND ISNUMERIC(o.quantity) = 1 AND ISNUMERIC(o.pointperitem) = 1 )