Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
 Posted - 2012-09-07 : 17:08:42
|
| Hi friends,I am having a query which calcualtes the sum of purchases by state in the following wayselect sum(case when customer_purchase_date is not null then customer_price end ) as customer_purchase_price ,statefrom Customerwhere customer_purchase_date between '' and ''group by stateResultCustomer_purchase_price state10 AK20 TXIn the above query when there is no data from particular date range the result set is showing up as emptyBut is there a way for me to show a zero for each state instead of empty result set....0 Ak0 TXThe states are Just The two...Arkansas an texas...Thank you |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-07 : 17:31:38
|
| Try this:select sum(case when customer_purchase_date is not null then customer_price else 0 end ) as customer_purchase_price ,statefrom Customerwhere customer_purchase_date between '' and ''group by state-Chad |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-09-07 : 17:47:46
|
| [CODE]select a.State, coalesce(b.customer_purchase_price, 0)from ( select distinct State from @Customer ) aleft outer join ( select sum(case when customer_purchase_date is not null then customer_price end ) as customer_purchase_price ,state from Customer where customer_purchase_date between '20120201' and '20120301' group by state ) b on a.state = b.state[/CODE]=================================================We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry |
|
|
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2012-09-08 : 14:58:29
|
| thank you all for your responses...Bustaz Kool response worked perfectly.. |
|
|
|
|
|
|
To show sum as 0 for null Values ?