Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

12/27/22 15:07:07 (2 years ago)

Author:

201084

Comment:

--

AdvancedReports

@@ -65 +65 @@
         order by vkupna_suma desc
 }}}
+=== Извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија ===
+{{{#!sql
+select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name ,
+count(ocp.id_part) as category_by_car
+from project.category as c
+left join project.part_is_from_category pifc on pifc.id_category = c.id_category 
+left join project.part p on p.id_part = pifc.id_part 
+left join project.order_contains_part ocp on ocp.id_part = p.id_part 
+left join project.order_table ot on ot.id_order = ocp.id_order 
+left join project.users_table ut on ut.id_user = ot.id_user 
+left join project.car_sample cs on cs.id_user = ut.id_user 
+left join project.car c2 on c2.id_car = cs.id_car 
+left join project.car_manufacturer cm on cm.id_car_manufacturer = c2.id_car_manufacturer 
+group by c.id_category,cm.id_car_manufacturer  
+order by category_by_car desc
+}}}
+
+=== Извештај за најкупуван артикл, во која количина и од која категорија е истиот ===
+{{{#!sql 
+select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
+        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
+        left join project.category c on c.id_category = pifc.id_category  
+        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
+        group by c.id_category , p.id_part  
+        having coalesce(sum(ocp2.quantity_order), 0)=(
+                select max(maxkol) from
+                (
+                        select coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
+                        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
+                        left join project.category c on c.id_category = pifc.id_category  
+                        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
+                        group by c.id_category , p.id_part 
+                ) q1
+                )
+}}}