I need help with an SQL Query

[Athul]

I have never done any calculations with sql query, so I have no idea how to do this so please help.

 

well i have two tables acc_tbl and ledger_tbl ( screenshots ) i need to find the the difference between total debit and total credit of that particular ledger debit (Dr) and credit(Cr) everything in screenshot. 

All i know was to join the table and select all thr columns available. 

[Barand]

Sorry, I gave it a try but I couldn't come up with a query that gave your expected results from the data provided.

(Perhap it's because pictures of data don't load very well into my test database)

I could only get

+-------------+---------+---------+
| ledger_code | Debit   | Credit  |
+-------------+---------+---------+
|           5 |  300.00 | 2400.00 |
|          18 | 2400.00 |         |
|          30 |         |  300.00 |
|             | 2700.00 | 2700.00 |
+-------------+---------+---------+

What queries have you tried?

[Athul]

54 minutes ago, Barand said:

Sorry, I gave it a try but I couldn't come up with a query that gave your expected results from the data provided.

(Perhap it's because pictures of data don't load very well into my test database)

I could only get

+-------------+---------+---------+
| ledger_code | Debit   | Credit  |
+-------------+---------+---------+
|           5 |  300.00 | 2400.00 |
|          18 | 2400.00 |         |
|          30 |         |  300.00 |
|             | 2700.00 | 2700.00 |
+-------------+---------+---------+

What queries have you tried?

Iam a beginer so all i did is just joined the two tables and selected all the colums necessary.I was Stuck there. I didnt know what to do next .

Could u please explain how you got that?

I could not upload .sql so i renmed to txt.

This is thr DB. 

transaction_db.txt

[Athul]

4 minutes ago, Athul said:

Iam a beginer so all i did is just joined the two tables and selected all the colums necessary.I was Stuck there. I didnt know what to do next .

Could u please explain how you got that?

I could not upload .sql so i renmed to txt.

This is thr DB. 

transaction_db.txt 3.79 kB · 0 downloads

This is all i've done 

[Barand]

The way to get the Dr and Cr totals is to use a group aggregation query. It seems strange that this wasn't covered in your course.

Example

mysql> SELECT a.ledger_code
    ->      , type
    ->      , SUM(amount) as total
    -> FROM acc_tbl a
    -> GROUP BY ledger_code, type;
+-------------+------+-------+
| ledger_code | type | total |
+-------------+------+-------+
| 18          | Dr   |  2400 |
| 30          | Cr   |   300 |
| 5           | Cr   |  2400 |
| 5           | Dr   |   300 |
+-------------+------+-------+

 

 

[Athul]

1 hour ago, Barand said:

The way to get the Dr and Cr totals is to use a group aggregation query. It seems strange that this wasn't covered in your course.

Example

mysql> SELECT a.ledger_code
    ->      , type
    ->      , SUM(amount) as total
    -> FROM acc_tbl a
    -> GROUP BY ledger_code, type;
+-------------+------+-------+
| ledger_code | type | total |
+-------------+------+-------+
| 18          | Dr   |  2400 |
| 30          | Cr   |   300 |
| 5           | Cr   |  2400 |
| 5           | Dr   |   300 |
+-------------+------+-------+

 

 

I didn't do any course I am learning through yt and other sites

[Barand]

I assumed a course as you seem to to have some sort of assignment ...

[Athul]

44 minutes ago, Barand said:

I assumed a course as you seem to to have some sort of assignment ...

Nope. It was an interview question .Its not even mine a friend asked me for help. I didnt know how to do so i asked here.

[Barand]

OK. Have you done it?

[Athul]

15 hours ago, Barand said:

OK. Have you done it?

Nope. Iam not quiet getting the exact expected output

[Barand]

Does this make it easier?

SELECT  a.ledger_code
      , l.ledger_name
      , SUM(IF(type='Dr',amount, null)) as Debits
      , SUM(IF(type='Cr',amount, null)) as Credits
FROM acc_tbl a
     JOIN ledger_tbl l USING (ledger_code)
GROUP BY a.ledger_code;

+-------------+---------------------+--------+---------+
| ledger_code | ledger_name         | Debits | Credits |
+-------------+---------------------+--------+---------+
| 18          | Maintenance         |   2400 |         |
| 30          | Annual Subscription |        |     300 |
| 5           | cash                |    300 |    2400 |
+-------------+---------------------+--------+---------+

 

[Athul]

2 hours ago, Barand said:

Does this make it easier?

SELECT  a.ledger_code
      , l.ledger_name
      , SUM(IF(type='Dr',amount, null)) as Debits
      , SUM(IF(type='Cr',amount, null)) as Credits
FROM acc_tbl a
     JOIN ledger_tbl l USING (ledger_code)
GROUP BY a.ledger_code;

+-------------+---------------------+--------+---------+
| ledger_code | ledger_name         | Debits | Credits |
+-------------+---------------------+--------+---------+
| 18          | Maintenance         |   2400 |         |
| 30          | Annual Subscription |        |     300 |
| 5           | cash                |    300 |    2400 |
+-------------+---------------------+--------+---------+

 

How long have you been programing?? I didn't even know tat we could use conditional statements inside queries. Well thank you very much for your time and this lesson God bless.

 

[Barand]

6 minutes ago, Athul said:

How long have you been programing?

• 53 years designing and developing business systems and applications,
• 37 years databases and SQL,
• 21 years PHP

7 minutes ago, Athul said:

I didn't even know tat we could use conditional statements inside queries

https://dev.mysql.com/doc/refman/5.7/en/functions.html

[Athul]

3 hours ago, Barand said:

• 53 years designing and developing business systems and applications,
• 37 years databases and SQL,
• 21 years PHP

https://dev.mysql.com/doc/refman/5.7/en/functions.html

 Almost 2 months in php.