in MySQL how do i get the total amount of components if one column is related to another?-CodePudding

I have a few machines reporting status on hardware components. not all machines have the same amount of hardware components. I need to know the total amount of components of all the machines

using this quary

SELECT
  time AS "time",
  machine_id,
  hw_id
FROM hardware
WHERE
  $__unixEpochFilter(time) AND
  component = 'gpu'
ORDER BY time

enter image description here

In this case, there are 9 components in total

CodePudding user response：

You can create an unique id by matching both ids. You'll get what you need with something like:

SELECT
  COUNT(DISTINCT(machine_id '/' hw_id)),
  machine_id,
  hw_id
FROM hardware
WHERE
  $__unixEpochFilter(time) AND
  component = 'gpu';

CodePudding user response：

Just count the records returned by the filter condition in your WHERE clause:

SELECT COUNT(*) AS count
  FROM hardware
 WHERE $__unixEpochFilter(time) AND component = 'gpu';