Solution to “#1242 - Subquery returns more than 1 row” error

286
May 04, 2017, at 3:13 PM

This is my query

SELECT
    a.ClassCode,
    a.Description,
    d.BusinessUnit,
    d.BUDes,
    d.Department,
    (SELECT COUNT(c.AssetGroup)
     FROM tbl_fa_asset_reg c
     GROUP BY c.AssetGroup) AS total
FROM tbl_fa_asset_reg_mas_b_class a
    JOIN tbl_fa_asset_reg_mas_c_group b ON a.ClassCode = b.AssetClass
    JOIN tbl_fa_asset_reg c ON c.AssetGroup = b.AssetGroup
    JOIN tbl_a_comp_f_bu d ON d.BusinessUnit = c.BusinessUnit
GROUP BY a.ClassCode, a.Description, d.BusinessUnit, d.BUDes, d.Department
ORDER BY ClassCode ASC
Answer 1

You could update your query like this

SELECT
    a.ClassCode,
    a.Description,
    d.BusinessUnit,
    d.BUDes,
    d.Department,
    c.Total
FROM tbl_fa_asset_reg_mas_b_class a
    JOIN tbl_fa_asset_reg_mas_c_group b ON a.ClassCode = b.AssetClass
    JOIN 
    (SELECT COUNT(c.AssetGroup) AS Total
     FROM tbl_fa_asset_reg c
     GROUP BY c.AssetGroup) c ON c.AssetGroup = b.AssetGroup
    JOIN tbl_a_comp_f_bu d ON d.BusinessUnit = c.BusinessUnit
GROUP BY a.ClassCode, a.Description, d.BusinessUnit, d.BUDes, d.Department
ORDER BY ClassCode ASC
Rent Charter Buses Company
READ ALSO
how to prevent secretary from inserting a doctor availability similar to another's doctor of same speciality using sql query?

how to prevent secretary from inserting a doctor availability similar to another's doctor of same speciality using sql query?

I have a windows form application C# for a medical clinic that a secretary have to use it to insert doctor's avaibilitiesI want to prevent the secretary from inserting similar avaibility to another's doctor of same speciality in the same day

377
setting minimum selectable date as 15 years past birthday (from mySQL) and max as current date

setting minimum selectable date as 15 years past birthday (from mySQL) and max as current date

I have a mySQL table of users containing the users' birthdaysi have stored the date from sql as the variable DateOfBirth

302
Mysql : select agregated values in between dates

Mysql : select agregated values in between dates

I've got mysql query (generated by zf2), it looks like

249
Pyrocms, stream on where clause

Pyrocms, stream on where clause

I use pyrocms to develop a siteI need to use stream for a view

278