CODEWITHSUNDEEP
mysqlsql
noober
noober

Reputation: 1505

How to count and create a sum of those counts

I need help constructing my query to do a count of system type id's.

Example:

SystemTypeID  Description           Acronym
1             System1 description   ABC
2             System2 description   DEF
3             System3 description   XYZ

I'm looking to construct something like this:

Count  SystemTypeID  Description           Acronym
21     1             System1 description   ABC
100    2             System2 description   DEF
67     3             System3 description   XYZ

So far, I know how to do a distinct, but I'm looking to count the number of occureneces for the SystemTypeID and then group it:

select distinct(a.SystemTypeID), 
       b.Description, 
       b.Acronym
from operations.Systems a,
     operations.SystemTypes b  
where a.SystemTypeID=b.SystemTypeID 
order by a.SystemType_ID;

Upvotes: 0

Views: 50

Answers (1)

ekad
ekad

Reputation: 14614

Instead of DISTINCT, what you need is COUNT and GROUP BY

SELECT COUNT(a.SystemTypeID) AS `Count`, 
       a.SystemTypeID,
       b.Description, 
       b.Acronym
FROM operations.Systems a,
     operations.SystemTypes b  
WHERE a.SystemTypeID = b.SystemTypeID
GROUP BY a.SystemTypeID,
       b.Description, 
       b.Acronym
ORDER BY a.SystemType_ID;

Upvotes: 1

Related Questions