A list of data after guess sum before a list of data, how to write SQL statements? Pray god to guide-CodePudding

Data as shown above, the desired result is that the grouping by id, and then judge condition is lend_time, if there is a value lend_time, then sum, according to the principal and if lend_time is empty, the display is 0, but the problem is with id 1 lend_time only one, principal has two, show and I can only display 1000, rather than 6000 (1000 + 5000), which means there are lend_time value summation, when no lend_time belong to a value of 0, beg god for help,

CodePudding user response:

Suggest you list your table structure, and provide the test data, and based on these test data that corresponds to the correct result,
Refer to the way the post questions http://bbs.csdn.net/topics/320211382

1. You create table XXX.. Statement
2. You insert into XXX... Statement
3. The result is what kind, and give a simple algorithm description)
4. Do you use the database name and version of (often asked in MS SQL server edition MySQL)

So people who want to help you to build the same environment with you directly, and in providing the test before, avoid the error in the text understanding,

CodePudding user response:

Select id, the sum (the principal) from TB where id in (select id from TB where lend_time is not null)

CodePudding user response:

 SELECT the IF (ISNULL (a), 0, b) AS the FROM b (
SELECT SUM (UNIX_TIMESTAMP (lend_time)) AS a, the SUM (principal) AS b 
FROM the test 
GROUP BY id 
) AS t

Is probably so, actually very simple, is your problem set said not clear

CodePudding user response:

refer to the second floor ZJCXC response:

select id, the sum (the principal) from TB where id in (select id from TB where lend_time is not null)

He also lend_time principal display 0 is empty

CodePudding user response:

 select id, case when Max (lend_time) is null then 0 else sum (principal) end the from TB group by id

CodePudding user response:

Junction post rate: 0%

When you the solutions of the problems please post.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html

8, how to give points and knot stick?
http://bbs.csdn.net/help#post_dispose

CodePudding user response:

Thank you very much for the two solutions, but I had a test or not, I need the result is that

CodePudding user response:

SELECT id, CASE WHEN lend_time IS NULL THEN 0 ELSE principal END AS principal
The FROM tbl_name
GROUP BY id

CodePudding user response:

Try this

 SELECT id, 
The SUM (CASE WHEN lend_time IS NULL THEN 0 
The ELSE principal 
END) AS principal 
FROM the Table 
GROUP BY id

CodePudding user response:

 SELECT 
T.i d, 
CASE 
The WHEN t.l end_time IS NULL THEN 0 
The ELSE t.p rincipal 
The END of total 
The FROM 
(SELECT 
Anderson, d, a. end_time, Amy polumbo rincipal 
The FROM 
Test_qty1 a 
GROUP BY Anderson, d) t;