如何根据不同的标准做两个MySQL SUM [英] How to do two MySQL SUMs based on different criterias

问题描述

我有6个彼此相关的表.我有一个收集所有数据的JOIN查询.我首先提供cpv_id作为来自用户的变量，然后最后我需要获取与该cpv_id相关的每个公司的报告(通过它们之间的许多表).

I have 6 tables related to each other. I have a JOIN query gathering all data. I am starting by providing cpv_id as a variable coming from users, and then at the end I need to get reports for each company that is related to that cpv_id ( through many tables between them ).

基本上，这是流程:

我从用户那里获得了 cpv_id ，我在 club_offer_cpv 表中找到了它，并且得到了 club_offer_id ，因此我可以连接到 club_offer 表.然后在 club_offer 表中，我得到 club_id ，这样我就可以连接到 club 表.然后在 club 表中，我得到 users_id ，这样我就可以连接到 users 表.然后在 users 表中，我得到 company_id ，以便我可以连接到 company 表.在 company 表中，我得到 special_id ，因此我可以连接到最后一个表 company_reports .这是我需要从中获取数据的地方.我需要这个:

I get cpv_id from users, I find it in club_offer_cpv table and I get club_offer_id so I can connect to club_offer table. Then in club_offer table I get club_id so I can connect to club table. Then in club table I get users_id so I can connect to users table. Then in users table I get company_id so I can connect to company table. In company table I get special_id so I can connect to the last table company_reports. This is where I need to get my data from. And I need this:

我在这里有3个感兴趣的领域: report_year ， variable_code 和 variable_value .

Here I have 3 field of interest: report_year, variable_code and variable_value.

report_year 存储制作公司报告的年份.
variable_code 存储所生成报告的类型.在我关于SQL Fiddle的简化示例中，可能的值为 AOP 605 和 AOP 201 .
variable_value 存储每个variable_code的值.例如，对于 AOP 605 代码，其值为 5 .

report_year stores the year when company report was made.
variable_code stores the type of report that was made. In my simplified example on SQL Fiddle, possible values are AOP 605 and AOP 201.
variable_value stores the value for each variable_code. For example for AOP 605 code there will be value of 5.

这是我的麻烦:我需要为属于某个cpv_id组(来自用户请求)的所有公司的每个variable_code求和variable_value.我只设法创建将对一个代码的值求和的查询.您可以在提供的SQL Fiddle中看到: http://sqlfiddle.com/#!2/a3e296/2

Here comes my trouble: I need to sum variable_value for each variable_code for all companies that belong to some cpv_id group ( came from user request ). I only managed to create query that will sum values for one code. You can see that in SQL Fiddle provided: http://sqlfiddle.com/#!2/a3e296/2

在输出中，您将看到我正在为代码 AOP 201 做totalSum，在我的应用程序中，我将再执行一个查询以计算 AOP 605 的总和，但是我认为这并不理想，并且可能有某种方法可以在同一查询中同时获取 AOP 201 和 AOP 605 的SUM.我可以先调用它们sum201和sum605并在我的应用程序中将它们显示出来.有人可以告诉我这是否可行吗?您能告诉我查询将收集 AOP 201 和 AOP 605 的总和并将它们存储在单独的值中，以便我可以显示 AOP 201 的总和的查询吗>以及 AOP 605 ???

In the output you will see that I am doing totalSum for code AOP 201, and In my application I am executing one more query to calculate sum for AOP 605, but I believe that is not ideal, and probably there is some way to get SUMs for both AOP 201 and AOP 605 in the same query. I can call them then sum201 and sum605 and display them both in my application. Can someone please tell me if this is possible ? Can you show me the query that will collect sums for AOP 201 and AOP 605 and store them in separate values so I can display sum for AOP 201 and sum for AOP 605 ???

还有另外一个棘手的部分.正如您在查询中看到的那样，我遇到这种情况:AND company_report.report_year = 2013.这是不理想的，因为有些公司不会在2013年提交报告，而有些公司只提供2012年的报告.我必须拿走他们所拥有的最后一年的报告.因此，举例来说，如果某家公司有2012年和2013年的报告，那么我将只考虑2013年.同样，如果其他公司有2009年和2012年的报告，则我只计算2012年.

Also there is one more tricky part. As you can see in my query I am having this condition: AND company_report.report_year = 2013. This is not ideal, because some companies will not have reports made in 2013, some of them have only for 2012. I would have to take whatever is the last year of report that they have. So for example if some company have reports in 2012 and 2013, I will take only 2013 in count. Similarly, if some other company have reports for 2009 and 2012, I will take only 2012 in count.

您还能帮我吗?

我为此苦苦挣扎了3天，无法找到解决方案.谢谢您的时间.

I am struggling with this for 3 day now, and can not find solution. Thank you for your time.

我需要 AOP 605 和 AOP 201 的总和作为单独的列，因此我可以在我的应用程序代码中访问它们，例如$ sumOne-显示 AOP 605 和$ sumTwo-显示 AOP 201 的总和.

I need sums of AOP 605 and AOP 201 as seprate columns, so I can access them in my application code like $sumOne - displays the sum for AOP 605 and $sumTwo - displays the sum for AOP 201.

更多信息:我正在尝试将这两个查询合并为一个:

MORE INFO: I am trying to merge these two queries into one:

SELECT DISTINCT company_report.*, floor(sum(variable_value)) as totalSum
                                         FROM company_report
                                         JOIN company        ON company_report.special_id = company.special_id 
                                         JOIN users          ON company.id = users.company_id 
                                         JOIN club           ON users.id = club.users_id
                                         JOIN club_offer     ON club.id = club_offer.club_id
                                         JOIN club_offer_cpv ON club_offer.id = club_offer_id 
                                                             AND club_offer_cpv.cpv_id LIKE '66%'
                                                             AND company_report.variable_code = 'AOP 201'
                                                             AND company_report.report_year = 2013

AND

SELECT DISTINCT company_report.*, floor(sum(variable_value)) as totalSum
                                         FROM company_report
                                         JOIN company        ON company_report.special_id = company.special_id 
                                         JOIN users          ON company.id = users.company_id 
                                         JOIN club           ON users.id = club.users_id
                                         JOIN club_offer     ON club.id = club_offer.club_id
                                         JOIN club_offer_cpv ON club_offer.id = club_offer_id 
                                                             AND club_offer_cpv.cpv_id LIKE '66%'
                                                             AND company_report.variable_code = 'AOP 605'
                                                             AND company_report.report_year = 2013

在每个查询中请注意以下几行:

Please pay attention on these lines in each query:

company_report.variable_code = 'AOP 605'

company_report.variable_code = 'AOP 201'

因此，在一个查询中，我想为variable_code ="AOP 605"的行获取variable_value的总和，在第二个查询中，我需要为variable_code ="AOP 201"的行获取variable_value的总和.但是我认为必须有某种方法可以在一个查询中同时获得两个和，但是在我可以分别将它们显示为机器人的方式上，例如:

So in one query I want to get sum of variable_value for rows having variable_code = "AOP 605", and in second I need to get sum of variable_value for rows having variable_code = "AOP 201". But I am thinking that there must be some way to get both sums in one query, but on the way that I can display them bot separately, like:

AOP 605的总和为123
AOP 201的总和是:345

Sum of AOP 605 is : 123
Sum of AOP 201 is : 345

推荐答案

您似乎正在寻找的是SELECT的GROUP BY部分，如下所示:

What you seem to be looking for is the GROUP BY part of a SELECT, like this:

SELECT DISTINCT company_report.*, floor(sum(variable_value)) as totalSum
FROM company_report
JOIN company        ON company_report.special_id = company.special_id 
JOIN users          ON company.id = users.company_id 
JOIN club           ON users.id = club.users_id
JOIN club_offer     ON club.id = club_offer.club_id
JOIN club_offer_cpv ON club_offer.id = club_offer_id 
                    AND club_offer_cpv.cpv_id LIKE '66%' 
                    AND company_report.variable_code IN ('AOP 201','AOP 605')
                    AND company_report.report_year = 2013
GROUP BY company_report.variable_code                    

是吗?

这篇关于如何根据不同的标准做两个MySQL SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！