CODEWITHSUNDEEP
mysql
douglas
douglas

Reputation: 1

Count data in MySQL

These are my tables:

tbl_fa_asset_reg

tbl_a_comp_f_bu

tbl_fa_asset_reg_mas_b_class

tbl_fa_asset_reg_mas_c_group

This is my query:

SELECT DISTINCT a.ClassCode,a.Description,d.BusinessUnit,d.BUDes,d.Department 
    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
    order by a.ClassCode ASC

The output I want:

Upvotes: 0

Views: 38

Answers (3)

douglas
douglas

Reputation: 1

Thanks but different from the output, there are some different data count, for example the results of calculations 16 actually should result 12

this is the output from

select a.ClassCode, a.Description, d.BusinessUnit, d.BUDes, d.Department, count(*) 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

enter image description here

Upvotes: 0

Unknown_Coder
Unknown_Coder

Reputation: 774

SELECT 
    DISTINCT 
      a.ClassCode,
      a.Description, 
      d.BusinessUnit,
      d.BUDes, 
      d.Department, 
      count(*)
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 
Order by a.ClassCode ASC

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

Seems you just need aggregation:

select a.ClassCode,
    a.Description,
    d.BusinessUnit,
    d.BUDes,
    d.Department,
    count(*) 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

Upvotes: 2

Related Questions