Changes between Version 7 and Version 8 of AdvancedReports

Timestamp:

12/27/22 21:22:06 (2 years ago)

Author:

201065

Comment:

--

AdvancedReports

@@ -112 +112 @@
 
 {{{#!sql
-        select s.supplierinfo, u.firstname, u.lastname, u.username, coalesce(sum(o2.quantity), 0) as koldostavena, coalesce(sum(o2.quantity*o2.price)) as zarabotkasupp from suppliers s 
+select s.supplierinfo, u.firstname, u.lastname, u.username, coalesce(sum(o2.quantity), 0) as koldostavena, coalesce(sum(o2.quantity*o2.price)) as zarabotkasupp from suppliers s 
                 left join users u on u.userid=s.userid
                 left join orders o on o.supplieruserid=s.userid and (o.status='Delivered' or o.status='Processed') 
@@ -118 +118 @@
                 group by s.userid, u.userid
                 order by zarabotkasupp desc
+}}}
 
+===  Извештај за најпопуларен артикл по категорија ===
 
+{{{#!sql
+select c2.categoryname,
+(select a.articlename from articles a
+        left join invoicedarticles i on i.articleid=a.articleid
+        left join article_belongs_to_category abtc on abtc.articleid=a.articleid
+        where abtc.categoryid = c2.categoryid 
+        group by a.articleid 
+        having coalesce(sum(i.quantity),0)=max(q1.vkolicina) limit 1),
+        max(q1.vkolicina) as vkolicinia from categories c2 
+                left join(
+                        select c.categoryid, c.categoryname, articlename, (coalesce(sum(i.quantity),0)) as vkolicina from categories c
+                        left join article_belongs_to_category abtc on abtc.categoryid=c.categoryid
+                        left join articles a on a.articleid=abtc.articleid
+                        left join invoicedarticles i on i.articleid=a.articleid 
+                        group by c.categoryid, a.articleid ) q1 on q1.categoryid=c2.categoryid 
+        group by c2.categoryid
 }}}