Everyone a great god, and in a small staff, want to ask a small problem, below this table, I'd like to list each device according to the occurrence date, number of each failure type of failure,
The original data is as follows:

Want to query results are as follows:

CodePudding user response:

Select id, description, count (1) the number of times the from date table where happen between "start time" and "end time" group by id, description

CodePudding user response:

 SELECT device id, fault description, COUNT (1) FROM the table WHERE the CONVERT (DATE, CONVERT (VARCHAR (10), DATE)) BETWEEN start DATE AND end DATE GROUP BY device id, fault description 

CodePudding user response:

I'm sorry I'm sorry, I describe the wrong, is to want to get into this appearance,

CodePudding user response:

The

reference 3 floor chenghangstar response:

 SELECT device id, the fault description, COUNT (1) FROM the table WHERE the CONVERT (DATE, CONVERT (VARCHAR (10), DATE)) BETWEEN start DATE AND end DATE GROUP BY device id, fault description 

reference 1/f, ancient farming response:

select id, description, the count number of (1) the from date table where happen between "start time" and "end time" group by id, description

I'm sorry I'm sorry, I describe the wrong, is to want to get into this appearance,

CodePudding user response:

 SELECT device id, fault description, REPLACE (CONVERT (VARCHAR (10), date), '-', '), COUNT (1) FROM the table GROUP BY device id, the fault description, REPLACE (CONVERT (VARCHAR (10), date), '-', ') 

To join the Group by the date is ok

CodePudding user response:

refer to 6th floor chenghangstar response:

 SELECT device id, the fault description, REPLACE (CONVERT (VARCHAR (10), date), '-', '), COUNT (1) FROM the table GROUP BY device id, the fault description, REPLACE (CONVERT (VARCHAR (10), date), '-', ') 

To join the Group by the date it is ok to

Hello, the query result is: using the method of you


I want to put the date in column shows,