Hi! Please i want To retrieve from the table the total amount of Paymt grouped by date and Paymt like in description. Help!!😣

Question

TABLE:
[Date | Amt | Paymt ]
[2/2/18 2000 Crédit ]
[2/2/18 2000 Express]
[3/2/18 2000 Express]
[3/2/18 1000 Express]
[3/2/18 2000 Crédit ]
[4/2/18 2000 Express]
[4/2/18 2000 Express]

Output after sql query from database table

[Date | Express | Credit ]
[2/2/18 2000 2000 ]
[3/2/18 3000 2000 ]
[4/2/18 4000 0 ]

Answer 1

Considering that your table is named Table1, here's one solution:

SELECT Date, SUM(Ex) AS Express, SUM(Cr) AS Credit
FROM
(SELECT Date, SUM(Amt) AS Cr, 0 AS Ex, Paymt
FROM Table1
WHERE Paymt = 'Credit'
GROUP BY 1
UNION
SELECT Date, 0 AS Cr, SUM(Amt) AS Ex, Paymt
FROM Table1
WHERE Paymt = 'Express'
GROUP BY 1
) AS T
GROUP BY 1

ABDOUL Just let me know if that worked for you :)

Answer 2

No problem. By the way, if you like learning by doing, you can experiment with your queries and imported datasets here:
http://sqlfiddle.com

You can first build a schema (a table) and query it online. This is actually how I coined this query here ⬆️

Answer 3

Kuba Siekierzyński thank very Much for the query. It works very Well even thaugh it so complexe. Great thank also to rudolph flash for his proposal his query was a second good option for me and i've learn a new word in sql through you -> 'HAVING' it the first time i see it in a query. Greate thanks to you guys. We learn every day..

Answer 4

ok thank Kuba Siekierzyński i Will try it.

Answer 5

ABDOUL RAHIM NDANE PECHOU One more hint from me: if you are on Microsoft SQL Server 2005 or more, you can also learn about the PIVOT command. You would mainly use it because it can be built quite well dynamically, for example if you had not 2 distinct values for Paymt, but maybe 10 or 50.

Here is an example:
https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

Search around a bit if you are interested in similar solutions for MySQL or other spin-offs👍

If you deal with only two values to pivot you are super fine with the solutions provided, if you have more than 2 or 3, they become cumbersome to write and maintain at some point.

Answer 6

Pe Kie it's really through what you saying. In case of Many different values it become cumbersome.. But unfortunately that wasn't my situation. But i will check on the pivot command in case i face such situation in the future. thank again.