"dave" <davef@helixpoint.com> wrote in message
news:%23ms1fYxrDHA.732@TK2MSFTNGP09.phx.gbl...
> What am I missing here. When there is no records I want to return a 0. It
> does not do that???
>
> CREATE PROCEDURE GetMachSalesTotalsNonStock
> (
> @SalesCookieID nvarchar(50)
> )
> AS
> SELECT
> theSubTotalNonStock = SUM(CASE
> WHEN tempSalesDetail.SalesPrice Is Null THEN 0
> ELSE tempSalesDetail.SalesPrice
> END
> )
> FROM tempSales INNER JOIN
> tempSalesDetail ON tempSales.tempSalesID =
> tempSalesDetail.tempSalesID
> WHERE (tempSales.tempSalesQuidID = @SalesCookieID)
> GROUP BY tempSalesDetail.NonStock
> HAVING (tempSalesDetail.NonStock = 1)
> GO
>
>

"dave" <davef@helixpoint.com> wrote in message
news:%23ms1fYxrDHA.732@TK2MSFTNGP09.phx.gbl...
> What am I missing here. When there is no records I want to return a 0. It
> does not do that???
>
> CREATE PROCEDURE GetMachSalesTotalsNonStock
> (
> @SalesCookieID nvarchar(50)
> )
> AS
> SELECT
> theSubTotalNonStock = SUM(CASE
> WHEN tempSalesDetail.SalesPrice Is Null THEN 0
> ELSE tempSalesDetail.SalesPrice
> END
> )
> FROM tempSales INNER JOIN
> tempSalesDetail ON tempSales.tempSalesID =
> tempSalesDetail.tempSalesID
> WHERE (tempSales.tempSalesQuidID = @SalesCookieID)
> GROUP BY tempSalesDetail.NonStock
> HAVING (tempSalesDetail.NonStock = 1)
> GO
>
>

"dave" <davef@helixpoint.com> wrote in message
news:%23ms1fYxrDHA.732@TK2MSFTNGP09.phx.gbl...
> What am I missing here. When there is no records I want to return a 0. It
> does not do that???
>
> CREATE PROCEDURE GetMachSalesTotalsNonStock
> (
> @SalesCookieID nvarchar(50)
> )
> AS
> SELECT
> theSubTotalNonStock = SUM(CASE
> WHEN tempSalesDetail.SalesPrice Is Null THEN 0
> ELSE tempSalesDetail.SalesPrice
> END
> )
> FROM tempSales INNER JOIN
> tempSalesDetail ON tempSales.tempSalesID =
> tempSalesDetail.tempSalesID
> WHERE (tempSales.tempSalesQuidID = @SalesCookieID)
> GROUP BY tempSalesDetail.NonStock
> HAVING (tempSalesDetail.NonStock = 1)
> GO
>
>

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:eXyr2kxrDHA.2432@TK2MSFTNGP10.phx.gbl...
> What is it returning? I am assuming null, since you are summing all null
> values. For example:
>
> select sum(case when unitPrice is null then 0 else unitPrice end)
> from products
> where 1=2
>
> returns null. Add coalesce to 0:
>
> select coalesce (sum(case when unitPrice is null then 0 else unitPrice
> end),0)
> from products
> where 1=2
>
> This returns the proper value.
>
> --------------------------------------------------------------------------
--
> Louis Davidson (drsql@hotmail.com)
> Compass Technology Management
>
> Pro SQL Server 2000 Database Design
> http://www.apress.com/book/bookDisplay.html?bID=266
>
> Note: Please reply to the newsgroups only unless you are
> interested in consulting services. All other replies will be ignored :)
>
> "dave" <davef@helixpoint.com> wrote in message
> news:%23ms1fYxrDHA.732@TK2MSFTNGP09.phx.gbl...
> > What am I missing here. When there is no records I want to return a 0.
It
> > does not do that???
> >
> > CREATE PROCEDURE GetMachSalesTotalsNonStock
> > (
> > @SalesCookieID nvarchar(50)
> > )
> > AS
> > SELECT
> > theSubTotalNonStock = SUM(CASE
> > WHEN tempSalesDetail.SalesPrice Is Null THEN 0
> > ELSE tempSalesDetail.SalesPrice
> > END
> > )
> > FROM tempSales INNER JOIN
> > tempSalesDetail ON tempSales.tempSalesID =
> > tempSalesDetail.tempSalesID
> > WHERE (tempSales.tempSalesQuidID = @SalesCookieID)
> > GROUP BY tempSalesDetail.NonStock
> > HAVING (tempSalesDetail.NonStock = 1)
> > GO
> >
> >
>
>
>

"dave" <davef@helixpoint.com> wrote in message
news:OqviQsxrDHA.1060@TK2MSFTNGP12.phx.gbl...
> It gives me no column name??
>
> select coalesce (sum(case when tempSalesDetail.SalesPrice is null then 0
> else tempSalesDetail.SalesPrice
> end),0)
> FROM tempSales INNER JOIN
> tempSalesDetail ON tempSales.tempSalesID =
> tempSalesDetail.tempSalesID
> WHERE (tempSales.tempSalesQuidID =
> '00ba8a46-a794-443a-9044-d25fa55c0cbf')
> GROUP BY tempSalesDetail.NonStock
> HAVING (tempSalesDetail.NonStock = 1)
>
>
>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:eXyr2kxrDHA.2432@TK2MSFTNGP10.phx.gbl...
> > What is it returning? I am assuming null, since you are summing all
null
> > values. For example:
> >
> > select sum(case when unitPrice is null then 0 else unitPrice end)
> > from products
> > where 1=2
> >
> > returns null. Add coalesce to 0:
> >
> > select coalesce (sum(case when unitPrice is null then 0 else unitPrice
> > end),0)
> > from products
> > where 1=2
> >
> > This returns the proper value.
> >
>
> --------------------------------------------------------------------------
> --
> > Louis Davidson (drsql@hotmail.com)
> > Compass Technology Management
> >
> > Pro SQL Server 2000 Database Design
> > http://www.apress.com/book/bookDisplay.html?bID=266
> >
> > Note: Please reply to the newsgroups only unless you are
> > interested in consulting services. All other replies will be ignored :)
> >
> > "dave" <davef@helixpoint.com> wrote in message
> > news:%23ms1fYxrDHA.732@TK2MSFTNGP09.phx.gbl...
> > > What am I missing here. When there is no records I want to return a 0.
> It
> > > does not do that???
> > >
> > > CREATE PROCEDURE GetMachSalesTotalsNonStock
> > > (
> > > @SalesCookieID nvarchar(50)
> > > )
> > > AS
> > > SELECT
> > > theSubTotalNonStock = SUM(CASE
> > > WHEN tempSalesDetail.SalesPrice Is Null THEN 0
> > > ELSE tempSalesDetail.SalesPrice
> > > END
> > > )
> > > FROM tempSales INNER JOIN
> > > tempSalesDetail ON tempSales.tempSalesID =
> > > tempSalesDetail.tempSalesID
> > > WHERE (tempSales.tempSalesQuidID = @SalesCookieID)
> > > GROUP BY tempSalesDetail.NonStock
> > > HAVING (tempSalesDetail.NonStock = 1)
> > > GO
> > >
> > >
> >
> >
> >
>
>