All MySQL great god please write SQL sum of grouping-CodePudding

ID name date version value
1 A September 5th V1 2
2 A September 5th V2 3
3 B on September 5, V2 3
4 B September 5th V1 1

Table is

Now I need to write a SQL
The first field for the name and the date group average value
The second field name and the date and version=V1 the mean value of the
The second field name and the date and version=V2 the mean value of the

How to write SQL help great god

CodePudding user response:

 

USE the test; 

DROP TABLE 
IF the EXISTS ` KKK `; 

The create table KKK (
` ID ` smallint not null AUTO_INCREMENT, 
` name ` varchar (10) not null, 
` date ` varchar (10) not null, 
` version ` varchar (10) not null, 
` value ` int the not null default 0, 
The PRIMARY KEY (` id `) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

Insert KKK values 
In September (1, 'A', '5', 'V1, 2), 
In September (2, 'A', '5', 'V2, 3), 
In September (3, 'B', '5', 'V2, 3), 
In September (4, 'B', '5', 'V1', 1), 
In September (5, 'A', '6', 'V2, 3), 
In September (6, 'B', '6', 'V2, 3), 
In September (7, 'B', '7', 'V1', 1); 

Select the va, vb, vc, da from (select avg (value) va, date da from KKK group by date) as aaa left join (select avg vb (value), the date the db from KKK where version='V1' group by date) as BBB on triple-a. Da=BBB. Db left join (select avg (value) of vc, the date the dc from KKK where version="V2" group by date) as CCC on BBB. Db=CCC. Dc; 

Drop table KKK.

CodePudding user response:

 SELECT name, 
The date, 
AVG (value) AS avgvalue, 
The SUM (CASE WHEN version='V1' THEN the value 
The ELSE 0 
END)/SUM (CASE WHEN version='V1' THEN 1 
The ELSE 0 
END) AS v1avg, 
The SUM (CASE WHEN version='V2' THEN the value 
The ELSE 0 
END)/SUM (CASE WHEN version="V2" THEN 1 
The ELSE 0 
END) AS v2avg 
FROM table 
GROUP BY name, 
The date

CodePudding user response:

SELECT the name, date,
AVG (value) AS avgvalue,
AVG (CASE WHEN version='V1' THEN the value END) AS avgV1,
AVG (CASE WHEN version='V2' THEN the value END) AS avgV2
The FROM
The table name
GROUP BY
The name, the date

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:

The original poster is to the following data?
Name the date version avgvalue1 avgvalue2
A September 5th V1 2
A September 5th V2 3
A September 5 v3 1
A September 5th 2
On September 5th V2 3 B
September 5th V1 1 B
On September 5, 2 B
This is the result of two group statement after the union
The first statement:
Select concat_ws (' - ', the name, date and version) as unionfields, avg (value) as avgv1, 0 as avgv2
The from tname group by the name, date, version
The results are as follows:
Unionfields avgv1 avgv2
On September 5 - A - 2 V1 0
A - September 5th - V2 3 0
A - September 5th - v3 1 0
B - September 5th - V2 3 0
September 5 - B - 1 V1 0
The second statement:
The select concat_ws (' - ', the name, date) as unionfields, 0 as avgv1, Avg (value) as avgv2
The from tname group by name, the date
The results are as follows:
Unionfields avgv1 avgv2
A - September 5 0 2
B - September 5 0 2
Joint two result sets:
Use among union all
Select concat_ws (' - ', the name, date and version) as unionfields, avg (value) as avgv1, 0 as avgv2
The from tname group by the name, date, version
Union all
The select concat_ws (' - ', the name, date) as unionfields, 0 as avgv1, Avg (value) as avgv2
The from tname group by name, the date the order by unionfields desc
The result set is as follows:
Unionfields avgv1 avgv2
On September 5 - A - 2 V1 0
A - September 5th - V2 3 0
A - September 5th - v3 1 0
A - September 5 0 2
B - September 5th - V2 3 0
September 5 - B - 1 V1 0
B - September 5 0 2
Can use 'alternative 0