sql server - How to count only rows where are non-zero value

In SELECT use this construction with CASE statement:

...
count(*) as rowscount,

sum( case a.costamount when 0 then 0 else 1 end ) as rowscount_nonzero

ORACLE, SQL SERVER - Typical subselect

Typical SQL subselect for finding value:

update factory.an_turnover a
set
a.c_last_date = ( select max( bew_dat ) from factory.himtbw
                  where
                  tl_nr = a.tl_nr and
                  tl_fam = a.tl_fam and
                  tl_atn = a.tl_atn and
                  bew_art in ( 'PP', 'PV', 'PN' ) and 
                  lag_ort not in ( '700', '701' )
                 )
;

commit;

SQL SERVER - select with having clause

When you need only rows, where their count in group is for example greater than 1, use this code with having clause:

select prodid, itemid, index, count(*)
from cieb_ivecofvvlabels
group by prodid, itemid, index
having count(*) > 1;

Output:

prodid               itemid                                   index       count
-------------------- ---------------------------------------- ----------- -----------
17-045164            5801-285-899_0_0000                      1           2
17-045164            5801-285-899_0_0000                      2           2
17-045164            5801-285-899_0_0000                      3           2
17-045164            5801-285-899_0_0000                      4           2
17-045165            5801-285-899_0_0000                      1           2
...

Another variant is to use subselect on base select.

AX - How get customer name in external SQL select

select a.accountnum, b.name from custTable a
join dirPartyTable b
on b.recid = a.party
where
a.accountnum like 'MINSK32680'

Output:

accountnum           name
-------------------- -------------------------
MINSK32680           MINSK WHEEL TRACTOR PLANT

(1 row(s) affected)

SQL SERVER, ORACLE - How use conditional logic in select, case statement

Prevention zero divide error:

select b.inventlocationid, itemid, a.availphysical, a.postedqty, 
a.postedvalue / case 
                  when a.postedqty = 0 then 1 
                  else a.postedqty 
                end as cost_avg_mj
from inventsum a
join inventdim b
on b.inventdimid = a.inventdimid and
b.dataareaid = a.dataareaid
where
a.itemid like 'S-%' and
a.availphysical <> 0