sql - countif and sumif each row without grouping -

i'm trying figure out sumif , countif using sql

here code start with: select
itemid , supplier , cost , saleprice countifexpirement

i'm trying create output:

first 4 columns have data , i'm trying create calculated fields calculate duplicate items, items per supplier, , cost supplier. easy create in excel. there alot of examples use grouping methods, need every row present.

in standard sql use analytic functions this. not available in every dbms, however.

select    itemid, supplier, cost, saleprice,    count(*) on (partition itemid) count_itemid,    count(*) on (partition supplier) count_supplier,    sum(cost) on (partition supplier) cost_suppler countifexpirement; 

here same sub-select, available in every dbms:

select    itemid, supplier, cost, saleprice,    (     select count(*)      countifexpirement x      x.itemid = countifexpirement.itemid   ) count_itemid,    (     select count(*)      countifexpirement x      x.supplier = countifexpirement.supplier   ) count_supplier,    (     select sum(cost)     countifexpirement x      x.supplier = countifexpirement.supplier   ) cost_suppler countifexpirement;