CODEWITHSUNDEEP
sqlsql-server
Ali
Ali

Reputation: 5

SQL Join Query to Get Item Name , Date and Sum of Debit Balance where Debit balance Not Equal to 0?

SELECT dbo.items.NAME, 
       CONVERT(VARCHAR(20), dbo.warehouse.date, 101)AS Date, 
       Sum (dbo.warehouse.debit)                    AS Quantity 
FROM   dbo.warehouse 
       INNER JOIN dbo.items 
               ON dbo.warehouse.businessunitid = dbo.items.businessunitid 
                  AND dbo.warehouse.itemid = dbo.items.itemid 
GROUP  BY dbo.items.NAME, 
          CONVERT(VARCHAR(20), dbo.warehouse.date, 101)

SQL Join Query to Get Item Name , Date and Sum of Debit Balance where Debit balance Not Equal to 0

Upvotes: 0

Views: 114

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

It looks like you are using SQL Server.

SELECT i.NAME, 
       CONVERT(VARCHAR(20), w.date, 101 ) AS Date, 
       Sum(w.debit) AS Quantity 
FROM dbo.warehouse w INNER JOIN
     dbo.items i
     ON w.businessunitid = i.businessunitid AND
        w.itemid = i.itemid 
GROUP BY i.NAME, CONVERT(VARCHAR(20), w.date, 101) 
HAVING Sum(w.debit) > 0;

This assumes that w.debit is never negative. (In financial applications, dollar amounts are often always positive.) Of course, if it can be negative, you might want:

HAVING Sum(w.debit) <> 0;

or:

HAVING Sum(w.debit) < 0;

Depending on the circumstances.

In many other databases, you can use the alias in the HAVING clause:

HAVING Quantity > 0

but SQL Server doesn't support this.

Also note the use of table aliases. This makes it easier to write and to read queries.

Upvotes: 3

dani herrera
dani herrera

Reputation: 51715

You are looking for having clause. Append this to your query:

HAVING Sum (dbo.warehouse.debit) <> 0

Quoting HAVING (Transact-SQL) docs -- your sql looks like t-sql --

HAVING (Transact-SQL)

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Upvotes: 1

Related Questions