SQL statement problems encountered in charge system of computer room

Personal version of the computer room charge system is in progress, encountered a few questions about SQL statements.

Use of 1.sum functions:

In the checkout section, ask for the and of a column in a table. In the first computer room system, I do not know the existence of the sum function, very silly and naïve to use the loop to add a single. The following is the sum of all the cards in the balance of how to make, the SQL statement is: select SUM (Cash) from T_card. This format is not fixed and can be changed according to requirements, such as multi-column and: Select SUM (column name 1), SUM (column Name 2) ... from [table name] where .... The result of the query is only one row, if only a column of the and, is a row of one column, that and in DataTable.Rows (0). Item (0).

Use of 2.COALESCE functions:

The sum function above easily solves the problem of summation, and a problem arises. For example, check the amount of money on the day of the sum, but there is no recharge record, then return a "NULL", in the code how to deal with it? After several setbacks, found the COALESCE function. When the query does not match the result of the condition, we let it return "0", which is written in the SQL statement: SELECTCOALESCE (SUM (Cash), 0) from T_card where ...

3. Convert date conversion:

when the table was built, in order to save things date and time is not separated, using the datetime data type, so that the date and time of input is joined together. in the checkout time trouble out, such as I want to query "2014-8-7" recharge record, but my watch time is "2014-8-7 hh:mm:ss" format. in my desperate thought to change the table, lost a long list of expressions unclear to Baidu, incredibly found a solution, using convert date conversion.

The syntax of convert is: convert (type, expression/field, style), where the style is limited to the DateTime type, and "120" represents the format "2014-8-7 20:50:31". the specific SQL statement is written like this:

Select COALESCE (SUM (Addcash), 0) from T_recharge where (CONVERT (varchar), time, sum) = convert (varchar), Getdat E (), ()), which means the sum of the amount of the day's recharge is obtained from the recharge form.

Summary: Although a year ago to learn SQL statements, in the two systems are used many times, and in self-examination this part also occupies a certain amount of weight, but to go back and forth will write the most basic sentence. Through the above several problems encountered, let me feel that knowledge is really learning, at the same time also realize that the "not afraid of the teacher, do not know, do not know" meaning. In the face of vast knowledge, we can not be proficient in everything, but know its existence. Encounter problems, with the help of modern network resources to solve the real-time, so efficient learning, and constantly develop their learning ability.