Question 20614 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql subselect on group by

I have a table of locations

CREATE TABLE `asset_location_rel` (
  `a_id` int(11) NOT NULL,
  `asset_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `deleted` int(11) NOT NULL DEFAULT 0,
  `units` int(11) NOT NULL DEFAULT 1,
  `unit_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `locations` (`location_id`, `location_row`, `location_column`, `location_name`, `location_row_col`, `location_created_datetime`, `location_created_by_userid`, `location_deleted_datetime`, `location_deleted_by_userid`, `location_comment`) VALUES
(557, 'K', '41', 'F3', '', '2022-11-09 14:43:43', 1, '0000-00-00 00:00:00', 0, '');
COMMIT;

where multiple assets can be located at the same location

INSERT INTO `asset_location_rel` (`a_id`, `asset_id`, `location_id`, `deleted`, `units`, `unit_type`) VALUES
(1643, 2042, 31, 0, 1, 0),
(1645, 34, 0, 0, 0, 0),
(1646, 34, 58, 0, 1, 0),
(1657, 519, 557, 0, 1, 0),
(1658, 477, 557, 0, 2, 0);
COMMIT;

Now, listing the locations I would like to group by location_id so to not display all relations to the location.
But I also would like to summarize number of items on said location.

Testing with location 557

How to?

Tried

SELECT
  locations.*,
  (SELECT asset_location_rel.units
  FROM asset_location_rel
  WHERE
    asset_location_rel.location_id = locations.location_id)
    AS total_asset_units
FROM
  locations
LEFT JOIN asset_location_rel ON asset_location_rel.location_id = locations.location_id
WHERE
  locations.location_deleted_datetime = '0000-00-00 00:00:00'
GROUP BY
  locations.location_id
ORDER BY
  locations.location_id
DESC

with no luck : #1242 – Subquery returns more than 1 row

Edit:
Expected result:

ID    Lokation  Row/Column NumItems  
557   F3            K/41       3
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    EDIT EDIT: Solution (seems so atleast):

    SELECT
  locations.*,
  (SELECT SUM(asset_location_rel.units)
  FROM asset_location_rel
  WHERE
    asset_location_rel.location_id = locations.location_id)
    AS total_asset_units
FROM
  locations
LEFT JOIN asset_location_rel ON asset_location_rel.location_id = locations.location_id
WHERE
  locations.location_deleted_datetime = '0000-00-00 00:00:00'
GROUP BY
  locations.location_id
ORDER BY
  locations.location_id
DESC

    Seems SUM with Groupby in a subselect does the trick ;)


  2. 0

    This is an other way to do it using inner joins as follows :

    The subquery is to get the totalUnits by location, then with a join to the locations table we get the location row

    select l.*, s.totalUnits
from locations l
inner join (
  SELECT l.location_id, sum(alr.units) as totalUnits
  from locations l
  INNER JOIN asset_location_rel alr ON alr.location_id = l.location_id
  WHERE l.location_deleted_datetime = '0000-00-00 00:00:00'
  GROUP BY l.location_id
) as s on s.location_id = l.location_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search