计算列的总和并插入到其他表中。 [英] calculate the sum of column and insert in to other table.

问题描述

嗨...



我有一个餐桌名称食堂包含午餐信息，早餐费用......

 EMPID名称检查时间类别Emp_Contrii Comp_Contri TOTAL 
   -    ---- --------- ----- -------- ----------- -------- ----- ------ ----------  
 BIN0112 Rajendran V 11/9/2010 07:54 BREAKFAST  10   20   30  
 BIN0112 Rajendran V 12/4/2010 07:54 BREAKFAST  10   20   30  
 BIN0113 Anand J 6 / 29/2010 07:49 BREAKFAST  10   20   30  
 BIN0113 Anand J 6/30/2010 02:48午餐 27   33   60  
 BIN0114 xyz J 6/30/2010 07:48 BREAKFAST  10   20   30  
 BIN0113 Anand J 7/1/2010 07:36 BREAKFAST  10   20   30  
 BIN0117 Arvind 7/2/2010 07:49 BREAKFAST  10   20   30  
 BIN0113 Anand J 7/6/2010 07:39 BREAKFAST  10   20   30  
 BIN0119 Anna J 7/8 / 2010 02:27午餐 27   33   60  
 BIN0113 Anand J 7/9/2010 07:45 BREAKFAST  10   20   30  
 BIN0118 cmf J 7/10 / 2010 07:45 BREAKFAST  10   20   30  
 BIN0110 Anand J 7/12/2010 07:41 BREAKFAST  10   20   30  
 BIN0113 Anand J 7/13/2010 02:45午餐 27   33   60  

我还有一个食堂餐厅全部含有所有信息一个月的员工..

 EMPID名称GRAND Emp_Contrii GRAND Comp_Contri GRANDTOTAL 
   -    ---- ----------- -  ----------------- ----------------- -----------  
 BIN0111 a 
 BIN0112 b 
 BIN0113 c 
 BIN0114 d 
 BIN0115 e 
 BIN0116 f 
 BIN0117 g 
 BIN0118 h 
 BIN0119 i 
 BIN0120 j 
 BIN0121 k 
 BIN0122 l 
 BIN0123 m 

这里名称和EMPID存在...

现在我想编写一个查询来填充GRAND Emp_Contrii中Emp_Contrii的总和，对于两个日期之间的其他两列，其中两个表EMPID应该是相同。例如1/02/2013至30/02/2013。

解决方案

查看以下查询

  SELECT  EMPID，名称，总和（Emp_Contrii），sum（Comp_Contri），sum（Emp_Contrii）+ sum（Comp_Contri） as  TOTAL 
  FROM 食堂
  WHERE 检查时间  '  1-Jan-2013' 和 '  2013年1月30日' 
  GROUP   BY  EMPID，名称

以下查询将为您提供月度报告：

  SELECT   CONVERT （ VARCHAR （ 7 ），Checktime， 121 ）' 年月'，
 EMPID，
名称，
 SUM（Emp_Contrii）Total_Emp_Contrii，
 SUM（ Comp_Contri）Total_Comp_Contri，
 SUM（TOTAL）TOTAL 
  FROM  CANTEEN 
  GROUP   BY   CONVERT （ VARCHAR （< span class =code-digit> 7 ），Checktime， 121 ），EMPID，名称

您可以使用此SQL将选择结果插入另一个表：

 插入 到 canteenTotal（EMPID，Name，GRAND_Emp_Contrii，GRAND_Comp_Contri，GRANDTOTAL）
 选择 EMPID，N ame，sum（Emp_Contrii），sum（Comp_Contri），sum（TOTAL）
 从食堂
 其中 Checktime   '  1-Jan- 2013' 和 '  30-Jan- 2013' 
 组 按 EMPID，名称

Hi...

I have one Table name canteen contain the information lunch ,breakfast cost...

EMPID   Name  Checktime   Category     Emp_Contrii  Comp_Contri   TOTAL
------      ---------  -----   -------- -----------      --------   -----------  ----------
BIN0112     Rajendran V     11/9/2010 07:54   BREAKFAST   10          20          30  
BIN0112      Rajendran V    12/4/2010 07:54   BREAKFAST   10          20          30  
BIN0113      Anand J        6/29/2010 07:49   BREAKFAST   10          20          30  
BIN0113      Anand J        6/30/2010 02:48   LUNCH       27          33          60  
BIN0114      xyz J          6/30/2010 07:48   BREAKFAST   10          20          30  
BIN0113      Anand J        7/1/2010 07:36    BREAKFAST   10          20          30  
BIN0117      Arvind         7/2/2010 07:49    BREAKFAST   10          20          30  
BIN0113      Anand J        7/6/2010 07:39    BREAKFAST   10          20          30  
BIN0119      Anna J         7/8/2010 02:27    LUNCH       27          33          60  
BIN0113      Anand J        7/9/2010 07:45    BREAKFAST   10          20          30  
BIN0118      cmf J          7/10/2010 07:45   BREAKFAST   10          20          30  
BIN0110      Anand J        7/12/2010 07:41   BREAKFAST   10          20          30  
BIN0113      Anand J        7/13/2010 02:45   LUNCH       27          33          60  

And i have one more table canteenTotal conatiin the information of all the employee of one month..

EMPID   Name          GRAND Emp_Contrii GRAND Comp_Contri  GRANDTOTAL
------ ------------   -----------------  -----------------  ----------- 
BIN0111 a
BIN0112 b
BIN0113 c
BIN0114 d
BIN0115 e
BIN0116 f
BIN0117 g
BIN0118 h
BIN0119 i
BIN0120 j
BIN0121 k
BIN0122 l
BIN0123 m

here name and EMPID are present ...
now i want to write a query to fill the sum of Emp_Contrii in GRAND Emp_Contrii same for other two column between the two dates where both the tables EMPID should be same. for example 1/02/2013 to 30/02/2013.

解决方案

check below query

SELECT EMPID, Name, sum(Emp_Contrii), sum(Comp_Contri), sum(Emp_Contrii) + sum(Comp_Contri) as TOTAL
FROM canteen
WHERE  Checktime between '1-Jan-2013' and '30-Jan-2013'
GROUP BY EMPID, Name

The below query will give you monthly reports:

SELECT CONVERT( VARCHAR(7),Checktime,121)'Year-Month',
        EMPID,
        Name,
        SUM(Emp_Contrii) Total_Emp_Contrii,
        SUM(Comp_Contri) Total_Comp_Contri,
        SUM(TOTAL) TOTAL
FROM CANTEEN
GROUP BY CONVERT( VARCHAR(7),Checktime,121), EMPID, Name

You can use this SQL to insert the select result into another table:

Insert into canteenTotal (EMPID, Name, GRAND_Emp_Contrii, GRAND_Comp_Contri, GRANDTOTAL)
Select EMPID, Name, sum(Emp_Contrii), sum(Comp_Contri), sum(TOTAL)
From canteen
Where Checktime between '1-Jan-2013' and '30-Jan-2013'
Group by EMPID, Name

这篇关于计算列的总和并插入到其他表中。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！