Examples of MySQL row-and-column statistics queries

Source: Internet
Author: User
Tags lenovo


When we do a statistical query, sometimes we need to use the same date/position and other conditions of different information for the row of the statistics, this time will need the following methods to statistics, very convenient.
1. Table structure
> desc Repair_record;
+------------------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+-------------------+-----------------------------+
| ID | bigint (20) | NO | PRI | NULL | auto_increment |
| sn | varchar (255) |     NO | |                             NULL | |
| rack_id | varchar (255) |     NO | |                             NULL | |
| Machine_model | varchar (255) |     NO | |                             NULL | |
| Fault_type | varchar (255) |     YES | |                             NULL | |
| start_time | varchar (25) |     YES | |                             NULL | |
| Manufacturer_arivetime | varchar (25) |     YES | |                             NULL | |
| Roc_donetime | varchar (25) |     YES | |                             NULL | |
| Update_time | Timestamp |     NO | | Current_timestamp | On Update Current_timestamp |
| Fault_desc | varchar (2000) |     YES | |                             NULL | |
+------------------------+---------------+------+-----+-------------------+-----------------------------+
2. Table record
> select Id,machine_model,fault_type,start_time from Repair_record limit 10;
+----+---------------+------------+---------------------+
| ID | Machine_model | Fault_type | start_time |
+----+---------------+------------+---------------------+
| 2 | Lenovo RD530 | Disk | 2015-12-20 11:40:05 |
| 4 | Lenovo RD540 | Disk | 2015-12-20 21:40:07 |
| 6 | Dell R720 | Disk | 2015-12-20 23:00:07 |
| 8 | IBM X3630 M3 | Disk | 2015-12-21 10:20:06 |
| 10 | IBM X3650 M3 | Disk | 2015-12-21 15:40:04 |
| 12 | Dell r730xd | Disk | 2015-12-21 15:40:05 |
| 14 | IBM X3650 M3 | Disk | 2015-12-21 17:20:02 |
| 16 | Lenovo RD540 | Disk | 2015-12-21 17:40:03 |
| 18 | Dell r720xd | Disk | 2015-12-22 10:40:09 |
| 20 | Dell R720 | Disk | 2015-12-22 11:00:06 |
+----+---------------+------------+---------------------+
3. List of career change statistics
SQL command

>select Machine_model, COUNT (ID) total, sum (case fault_type when ' disk ' then 1 else 0-end) hard drive failure, sum (case fault_type when ' Mainboard ' then 1 else 0 end) motherboard, sum (case fault_type when ' raidcard ' then 1 else 0) RAID card, sum (case fault_type WH En ' backboard ' then 1 else 0 end) hard disk backplane, sum (case fault_type ' mem ' then 1 else 0) memory, sum (case fault_type when ' Power ' then 1 else 0-end ' supply, sum (case fault_type when ' CPU ' then 1 else 0) CPU, sum (case fault_type when ' Ilocard ') Then 1 else 0 end) ILO card from Repair_record GROUP by Machine_model ORDER by count (ID) desc;
Query results
+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+
| Machine_model | Total number | Hard Drive Failure | Motherboard | RAID Card | Hard Disk back panel | Memory | Power supply | CPU | ILO Card |
+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+
|     Dell r730xd |           61 |      58 |       1 |            0 |      0 |      1 |    0 |      0 | 0 |
|     Lenovo RD530 |           43 |      28 |       1 |            0 |      0 |     1 |    13 |      0 | 0 |
|     IBM X3650 M3 |           39 |      39 |       0 |            0 |      0 |      0 |    0 |      0 | 0 |
|     IBM X3550 M3 |           33 |      30 |       1 |            0 |      0 |      1 |    0 |      0 | 1 |
|     Dell R720 |           24 |      15 |       6 |            0 |      0 |      2 |    1 |      0 | 0 |
|     IBM X3630 M3 |           22 |      18 |       3 |            0 |      0 |      1 |    0 |      0 | 0 |
|     Dell r720xd |            20 |      7 |       5 |            3 |      3 |      0 |    0 |      2 | 0 |
|      Lenovo RD540 |            8 |      5 |       0 |            0 |      0 |      2 |    1 |      0 | 0 |
|      Lenovo rd350x |            5 |      5 |       0 |            0 |      0 |      0 |    0 |      0 | 0 |
|      Lenovo RD550 |            3 |      2 |       0 |            0 |      0 |      0 |    0 |      0 | 1 |
|      Dell T630 |            2 |      2 |       0 |            0 |      0 |      0 |    0 |      0 | 0 |
|      Lenovo RD330 |            1 |      1 |       0 |            0 |      0 |      0 |    0 |      0 | 0 |
|      Dell R630 |            1 |      1 |       0 |            0 |      0 |      0 |    0 |      0 | 0 |
|      Dell R730 |            1 |      0 |       0 |            0 |      1 |      0 |    0 |      0 | 0 |
+---------------+--------+--------------+--------+---------+--------------+--------+--------+------+--------+

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.