Count data in MySQL

Question

These are my tables:

tbl_fa_asset_reg

AssetID
AssetGroup
BusinessUnit
AssetGroup

tbl_a_comp_f_bu

Region
Districk
BusinessUnit

tbl_fa_asset_reg_mas_b_class

ClassCode
Description

tbl_fa_asset_reg_mas_c_group

id
AssetGroup
AssetClass

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:

Gurwinder Singh · Accepted Answer · 2017-05-04 04:16:03Z

2

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

answered May 4, 2017 at 4:16

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Unknown_Coder · Accepted Answer · 2017-05-04 04:17:49Z

0

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

answered May 4, 2017 at 4:17

Unknown_Coder

7726 silver badges24 bronze badges

Comments

douglas · Accepted Answer · 2017-05-04 07:03:40Z

0

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

answered May 4, 2017 at 7:03

douglas

11 bronze badge

Collectives™ on Stack Overflow

Count data in MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related