[bm1242]

I have a table (rlad_detected) with the following columns: logfilename, date, alarm.

The first 7 characters of logfilename contain a machine part number and the rest is a unique time stamp.

I would like to count the number of times a particular machine has an alarm. The following code allows me to select the distinct machine names from the table.

select distinct(substr(logfilename, 1, 7)) from rlad_detected; 

I have been wracking my brain on how to write the code to actually count the number of alarms that occurred for each machine. I came up with this, but it does not execute properly.

select alarm,Count(*) from rlad_detected

group by distinct(substr(logfilename, 1, 7)); 

When I do this, it highlight's the 'd' in distinct and says "missing expression," which is supposed to mean that I'm missing a required part of a clause, but I'm not sure what I've done wrong.

I'm really hoping someone could give me a hand here. I've never programmed in SQL before this week. Thanks. Hope life is good.

[James1992]

bm1242, on 12 October 2012 - 08:26 AM, said:

I have a table (rlad_detected) with the following columns: logfilename, date, alarm.

The first 7 characters of logfilename contain a machine part number and the rest is a unique time stamp.

I would like to count the number of times a particular machine has an alarm. The following code allows me to select the distinct machine names from the table.

select distinct(substr(logfilename, 1, 7)) from rlad_detected; 

I have been wracking my brain on how to write the code to actually count the number of alarms that occurred for each machine. I came up with this, but it does not execute properly.

select alarm,Count(*) from rlad_detected

group by distinct(substr(logfilename, 1, 7)); 

When I do this, it highlight's the 'd' in distinct and says "missing expression," which is supposed to mean that I'm missing a required part of a clause, but I'm not sure what I've done wrong.

I'm really hoping someone could give me a hand here. I've never programmed in SQL before this week. Thanks. Hope life is good.

I've only been doing SQL for a week now, but when using the Count function, surely you right that first?

SELECT Count(Alarm)
FROM rlad_detected
WHERE - state your constraint
GROUP BY... you won't need to right alarm here because you have already counted them. You also haven't selected any other attributes and so you don't need the group by.

[fuzz_collective]

Placement of count function does not matter.

[fuzz_collective]

SELECT COUNT(alarm),substr(logfilename,1,7) "Machine_Name" FROM rlad_detection
GROUP BY substr(logfilename,1,7);