| 89 | | === Извештај за категориите со најмногу продадени артикли=== |
| 90 | | |
| 91 | | {{{#!sql |
| 92 | | select c.categoryname, a.articlename, coalesce(sum(i.quantity),0) as vkol from articles a |
| 93 | | left join article_belongs_to_category abtc on abtc.articleid=a.articleid |
| 94 | | left join categories c on c.categoryid=abtc.categoryid |
| 95 | | left join invoicedarticles i on i.articleid=a.articleid |
| 96 | | group by c.categoryid, a.articleid |
| 97 | | having coalesce(sum(i.quantity), 0)=( |
| 98 | | select max(vkol) from |
| 99 | | ( |
| 100 | | select coalesce(sum(i.quantity),0) as vkol from articles a |
| 101 | | left join article_belongs_to_category abtc on abtc.articleid=a.articleid |
| 102 | | left join categories c on c.categoryid=abtc.categoryid |
| 103 | | left join invoicedarticles i on i.articleid=a.articleid |
| 104 | | group by c.categoryid, a.articleid |
| 105 | | ) q1 |
| 106 | | ) |
| 107 | | |
| 108 | | |
| 109 | | }}} |
| 110 | | |
| 111 | | === Извештај за набавувачите со најголема заработка=== |
| 112 | | |
| 113 | | {{{#!sql |
| 114 | | 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 |
| 115 | | left join users u on u.userid=s.userid |
| 116 | | left join orders o on o.supplieruserid=s.userid and (o.status='Delivered' or o.status='Processed') |
| 117 | | left join orderedarticles o2 on o2.orderid=o.orderid and (o2.articlestatus='Delivered' or o2.articlestatus='Processed') |
| 118 | | group by s.userid, u.userid |
| 119 | | order by zarabotkasupp desc |
| 120 | | }}} |
| | 109 | |
| | 110 | === Извештај за набавувачите со најголема заработка=== |
| | 111 | |
| | 112 | {{{#!sql |
| | 113 | 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 |
| | 114 | left join users u on u.userid=s.userid |
| | 115 | left join orders o on o.supplieruserid=s.userid and (o.status='Delivered' or o.status='Processed') |
| | 116 | left join orderedarticles o2 on o2.orderid=o.orderid and (o2.articlestatus='Delivered' or o2.articlestatus='Processed') |
| | 117 | group by s.userid, u.userid |
| | 118 | order by zarabotkasupp desc |
| | 119 | }}} |
