How to create the sum of a column with LIKE statement in sql?-CodePudding

I have a sql table as follows,

select * from Daily_Usage;

 ------------ ------------------- ------------- 
| Date       | Provider          | Total_Usage |
 ------------ ------------------- ------------- 
| 2022-01-11 | X1_IC_03          |       22.62 |
| 2022-01-11 | X1_IC_04          |       24.81 |
| 2022-01-11 | X1_IC_05          |       23.90 |
| 2022-01-11 | X2_IC_1           |        0.00 |
| 2022-01-11 | X3_08             |       16.86 |
| 2022-01-11 | X4_IC_01          |        0.00 |
| 2022-01-11 | X4_IC_02          |        0.00 |
| 2022-01-11 | X5_IC_02          |        6.66 |
| 2022-01-11 | X6_12             |       38.66 |
| 2022-01-11 | X6_13             |       25.27 |
| 2022-01-11 | X6_14             |     3205.78 |
| 2022-01-11 | X7_03             |        0.00 |
| 2022-01-11 | X8_05             |       11.32 |
| 2022-01-11 | X8_06             |        6.83 |
| 2022-01-11 | X8_07             |        0.53 |
| 2022-01-11 | X8_08             |        0.18 |
| 2022-01-11 | X8_09             |        0.98 |
| 2022-01-11 | X9_IC_03          |         0.00|
| 2022-01-11 | X10_01            |        0.00 |
| 2022-01-11 | X11_IC_07         |        0.11 |
| 2022-01-11 | X12_01            |     3178.98 |
 ------------ ------------------- ------------- 
21 rows in set (0.00 sec)

Let's take X1_IC_03 first,

In X1_IC_03 the main provider is X1 and it might contain anything in right side with X1, like X1%.So I need to get the sum of Total_Usageof all X1 values. So my output is as follows,

select * from Daily_Usage;

 ------------ ------------------- ------------- 
| Date       | Provider          | Total_Usage |
 ------------ ------------------- ------------- 
| 2022-01-11 | X1                |       71.33 |
| 2022-01-11 | X2                |        0.00 |
| 2022-01-11 | X3                |       16.86 |
| 2022-01-11 | X4                |        0.00 |
| 2022-01-11 | X5                |        6.66 |
| 2022-01-11 | X6                |      3269.71|
| 2022-01-11 | X7                |        0.00 |
| 2022-01-11 | X8                |       19.84 |
| 2022-01-11 | X9                |         0.00|
| 2022-01-11 | X10               |        0.00 |
| 2022-01-11 | X11               |        0.11 |
| 2022-01-11 | X12               |     3178.98 |
 ------------ ------------------- ------------- 
21 rows in set (0.00 sec)

I am going to get this output by using following query,

select 
    Date, Provider, sum(Total_Usage) 
from 
    Daily_Usage 
where 
    Provider like 'X1%' or Provider like 'X2%';

But it did not return what I need. It only provides the details of X1 as follows,

 ------------ ------------- ------------------ 
| Date       | Provider    | sum(Total_Usage) |
 ------------ ------------- ------------------ 
| 2022-01-11 | X1_IC_03    |            71.33 |
 ------------ ------------- ------------------

The provider name also not as requested. Can anyone show me how to do this?

CodePudding user response：

Group by is the right option for you. But you need to be careful. You can have different date values (in your case it's the same). In this case the script doesn't know which date to take. I used max, because you have only one value.

select 
    max(Date), LEFT(provider,LOCATE('_',provider) - 1), sum(Total_Usage) 
from 
    Daily_Usage 
group by  LEFT(provider,LOCATE('_',provider) - 1)