I want to count the number of occurrences of different sensor rows in SQL, but I seem to be doing it wrong and seemingly I am not visualizing it correctly.

If I was doing this in pseudocode in a C style language, I would do it like this:

FOR i in range(taglist[i]):
    print(taglist[i], count(taglist[i]) )

I have been trying this:

FROM   (
           SELECT DISTINCT [TagName]
           FROM   [A2ALMDB].[dbo].[AlarmMaster]
           WHERE  (TagName LIKE '%Sensor%' OR GroupName LIKE'%Sensors%')
       ) a

It returns 66, but I want it to return the count of each of the distinct tagnames that are returned in SELECT ... FROM (...)a.

Can anyone help me with how I should be trying to get all the counts of my different sensor occurrences to total instead of a count of all the distinct tagnames?

  • Just to clarify for any beginner who might come across this and get confused, this query as posted wouldn't work. If you run it, it will return an error: "Invalid column name 'a'". The COUNT function accepts either a column reference or a *, and the a in this case is a table reference, not a column. In the actual query probably either something else was there in place of Count(a) or the derived table's column was aliased as a too. – Andriy M Aug 11 at 8:34

Sounds like a simple group by:

select tagname, count(*)
from alarmmaster
where tagname like '%Sensor%'  
   or groupname like '%Sensor%'
group by tagname;
| improve this answer | |
  • thank you yes it does seem like i should have been trying to use group by that is doing what i was hoping. Hope this post is helpful for other sql beginners. thanks for the help – LLCoolSteve Aug 17 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.