grouping only rows which has a value in sql server

727 views sql
6

I have this record

atmno                          atmid       netpay
------------------------------ ----------- ---------------------------------------
1234123412345                  2           18426.68
NULL                           NULL        527.19
NULL                           NULL        4852.08
1234123412345                  1           4611.70
NULL                           NULL        1531.10

I want to group only the rows of the atmno which has a value. And for the other columns, i am taking the MIN of atmid and SUM of netpay. The desired output should look like this.

atmno                          atmid       netpay
------------------------------ ----------- ---------------------------------------
1234123412345                  1           23038.38
NULL                           NULL        527.19
NULL                           NULL        4852.08
NULL                           NULL        1531.10

This is what I've done so far.

SELECT atmno, MIN(atmid), SUM(netpay) FROM table GROUP BY atmno

You probably know that this query will also group the null values. My goal is to attain the desired result with just one query. Help will be greatly appreciated.

answered question

2 Answers

10

you can use 2 separate select query and then a UNION ALL to combine the result as one

SELECT atmno, MIN(atmid), SUM(netpay) 
FROM table 
WHERE atmno IS NOT NULL
GROUP BY atmno

UNION ALL

SELECT atmno, atmid, netpay
FROM   table
WHERE  atmno IS NULL

posted this
7

Select atmno, atmid, netpay 
from table t1, (select atmno, min(atmid), netpay
from table where atmno is not null 
group by atmid) t2
where t2.atmno =t1.atmno and t2.atmid = t1.atmid;
order by case when atmno is null then 1 else 0,
atmid

posted this

Have an answer?

JD

Please login first before posting an answer.