CODEWITHSUNDEEP
sql-serverselectsum
SHREE
SHREE

Reputation: 53

Selecting from two tables with 1st row from the second table

Table ACCOUNT(Name, Debit, Credit)

Name    | Debit  | Credit
=========================
Ram     | 2,000  | 2,000
Bheem   | 3,000  | 3,000
Soorya  | 2,500  | 1,750
John    | 3,500  | 2,500
Abdul   | 1,600  | 00000
Soorya  | 1,500  | 00000

Table CLIENTS(Name, ContactNumber)

Name   | ContactNumber
======================
Ram    | 900800
Bheem  | 900700
Soorya | 900600
John   | 900400
Abdul  | 900100
John   | No Value

SQL

SELECT Name, SUM(Debit), SUM(Credit) 
FROM ACCOUNT 
WHERE SUM(Credit)<>SUM(Debit) 
GROUP BY Name & ContactNumber 
FROM CLIENTS WHERE ACCOUNT.Name=CLIENTS.Name

If the Name of client exists twice, Only the 1st ContactNumber should be selected.

Expected result:

Name   | SUM(Debit) | SUM(Credit)  | ContactNumber
==================================================
Soorya | 4,000      | 1,750        | 900600
John   | 3,500      | 2,500        | 900400
Abdul  | 1,600      | 0000         | 900100

How do I to sort this problem?

Upvotes: 0

Views: 62

Answers (2)

yaquaholic
yaquaholic

Reputation: 152

Try using a JOIN statement, linked by the name fields.

SELECT a.Name, SUM(a.Debit), SUM(a.Credit), DISTINCT(c.ContactNumber) FROM ACCOUNT a WHERE SUM(a.Credit) != SUM(a.Debit) INNER JOIN CLIENTS c ON a.Name = c.Name GROUP BY a.Name

Hope it helps.

Upvotes: 0

Arve Hansen
Arve Hansen

Reputation: 1008

Not sure if this is the most elegant solution, but it gave the correct answer on the test data provided

WITH tmp
     AS (SELECT Name,
                Sum(Debit)  AS SumDebit,
                Sum(Credit) AS SumCredit
         FROM   accounts
         GROUP  BY Name)
SELECT a.Name,
       a.SumDebit,
       a.SumCredit,
       c.ContactNumber
FROM   tmp a,
       (SELECT Name,
               Max(ContactNumber) AS ContactNumber
        FROM   clients
        GROUP  BY Name) c
WHERE  a.Name = c.Name
       AND a.SumDebit <> a.SumCredit

Upvotes: 1

Related Questions