Deleting duplicate data using Multiple Table in MySQL

[Larry Alcantara]

Hello,

   I've been studying SQL for a past couple of weeks moreover I've been doing self-exercise on SQL by using "redash" platform or IDE and hereby would like to ask a question. I've created a database named "Retail Stores"  and I've created table names of the following: "store", "account", "order" "store_salesperson", and "store_warehouse_shipper. Please see the photo of the database table attached for reference:

My question is how can you produce or integrate "hub1" as one data ONLY or ONE result only on the HUB column without affecting the other columns of the tables? What i meant is the number of stores, name, # of Orders, Total Order Size, # of Boxes, Latitude, Longitude still appears the same result but "Hub1" data will only appear only once on the Hub Column.

By the way, here is the code of my self-taught exercise:

select (s.id) as '# of stores',
       s.name,
      (a1.display_name) as hub,
      count(o.id) as '# of orders',
      sum(total_product_cost) as 'Total Order Size',
      sum(o.no_of_boxes) as '# of boxes',
      s.point_y as latitude,
      s.point_x as longitude

from `order` as o
      join store_warehouse_shipper as sws on sws.id = o.associate_id
      join store as s on s.id = sws.store_id
      join store_salesperson as ss on ss.store_id = s.id
      join account as a1 on a1.id=ss.salesperson_account_id
      left join store_salesperson as ss1 on ss1.store_id = s.id and ss1.id>ss.id

where
      date(delivered_by) = curdate() - 1
      and a1.display_name like '%hub%'
     
      group by s.id, a1.id
      order by a1.id ASC


 

[Prasad Saya]

At present where is the query showing the output (the posted picture)? Is it some kind of report or tool? Some reporting software can possibly let do that in an application.

[Larry Alcantara]

I've already showed my query showing the output in my post. Again here is my query:

select (s.id) as '# of stores',
       s.name,
      (a1.display_name) as hub,
      count(o.id) as '# of orders',
      sum(total_product_cost) as 'Total Order Size',
      sum(o.no_of_boxes) as '# of boxes',
      s.point_y as latitude,
      s.point_x as longitude

from `order` as o
      join store_warehouse_shipper as sws on sws.id = o.associate_id
      join store as s on s.id = sws.store_id
      join store_salesperson as ss on ss.store_id = s.id
      join account as a1 on a1.id=ss.salesperson_account_id
      left join store_salesperson as ss1 on ss1.store_id = s.id and ss1.id>ss.id

where
      date(delivered_by) = curdate() - 1
      and a1.display_name like '%hub%'
     
      group by s.id, a1.id
      order by a1.id ASC