CODEWITHSUNDEEP
sqlsql-server
swat
swat

Reputation: 79

Select Data based on Groupby and some condition

I want to get data from an SQL table based on self join. Here is my example which can explain the problem in detail.

Table1:

CustNo|State|City|Year2014|Budget2014|Year2015|Budget2015|Year2016|Budget2016
123   |BW   |LA  |2014    |6789.87   |NULL    |NULL      |NULL    |NULL
234   |HH   |SS  |2014    |877.67    |NULL    |NULL      |NULL    |NULL
123   |BW   |LA  |NULL    |NULL      |2015    |8789.87   |NULL    |NULL
234   |HH   |SS  |NULL    |NULL      |2015    |569.45    |NULL    |NULL

From the above input I want the following output:

CustNo|State|City|Year2014|Budget2014|Year2015|Budget2015|Year2016|Budget2016
123   |BW   |LA  |2014    |6789.87   |2015    |8789.87   |NULL    |NULL
234   |HH   |SS  |2014    |877.67    |2015    |569.45    |NULL    |NULL

As we can see, for every year only the value of Budget field changes for a particular customer. I want to fetch the records in one row for one customer. I am not sure how I can get output like this.

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this using group by. Here is one method:

select CustNo, State, City,
       max(Year2014) as year2014,
       sum(Budget2014) as Budget2014
       max(Year2015) as year2015,
       sum(Budget2015) as Budget2015,
       max(Year2016) as year2016,
       sum(Budget2016) as Budget2016
from t
group by CustNo, State, City;

Upvotes: 1

Related Questions