3 Replies - 4070 Views - Last Post: 08 November 2012 - 07:06 PM

#1 bm1242  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 12-October 12

Error "missing expression" with a Group by

Posted 12 October 2012 - 08:26 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Error "missing expression" with a Group by

#2 James1992  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 130
  • Joined: 30-October 12

Re: Error "missing expression" with a Group by

Posted 02 November 2012 - 04:00 PM

View Postbm1242, 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.
Was This Post Helpful? 0
  • +
  • -

#3 fuzz_collective  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 02-August 12

Re: Error "missing expression" with a Group by

Posted 08 November 2012 - 06:55 PM

Placement of count function does not matter.
Was This Post Helpful? 0
  • +
  • -

#4 fuzz_collective  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 02-August 12

Re: Error "missing expression" with a Group by

Posted 08 November 2012 - 07:06 PM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1