COALESCE + NULL in Ms SQL CE

SUM an column in sqlCE

Page 1 of 1

5 Replies - 6289 Views - Last Post: 11 November 2008 - 02:27 PM Rate Topic: -----

#1 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

COALESCE + NULL in Ms SQL CE

Post icon  Posted 06 November 2008 - 12:56 PM

Hi,

i'm trying to SUM an column in sqlCE


SqlCeCommand cmd12 = new SqlCeCommand("SELECT SalesStandard SUM(COALESCE(SalesStandard, NULL)) AS SalesTotal FROM sales", connection);  
                        SqlCeDataReader reader12 = cmd12.ExecuteReader();

                          
                            while (reader12.Read())
                            {
                                //Console.WriteLine("test : {0}",reader12[0]);
                                

                                                               
                            }




the column have 0 in it, and it dosen't work, what am i doing wrong?

thanks

Is This A Good Question/Topic? 0
  • +

Replies To: COALESCE + NULL in Ms SQL CE

#2 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

Re: COALESCE + NULL in Ms SQL CE

Post icon  Posted 06 November 2008 - 12:56 PM

Hi,

i'm trying to SUM an column in sqlCE


SqlCeCommand cmd12 = new SqlCeCommand("SELECT SalesStandard SUM(COALESCE(SalesStandard, NULL)) AS SalesTotal FROM sales", connection);  
                        SqlCeDataReader reader12 = cmd12.ExecuteReader();

                          
                            while (reader12.Read())
                            {
                                //Console.WriteLine("test : {0}",reader12[0]);
                                

                                                               
                            }




the column have 0 in it, and it dosen't work, what am i doing wrong?

thanks
Was This Post Helpful? 0
  • +
  • -

#3 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: COALESCE + NULL in Ms SQL CE

Posted 06 November 2008 - 01:18 PM

COALESCE returns the first non-null expression from it's arguments.

maybe your query should be this...

SELECT COALESCE(SUM(SalesStandard), 0) AS 'SalesTotal'
FROM Sales

Was This Post Helpful? 1
  • +
  • -

#4 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

Re: COALESCE + NULL in Ms SQL CE

Posted 07 November 2008 - 04:11 AM

Hi,

dosent seems to work, i got :

The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = SUM ]

and

There was an error parsing the query. [ Token line number = 1,Token line offset
= 45,Token in error = SalesTotal ]

when using :
SqlCeCommand cmd12 = new SqlCeCommand("SELECT COALESCE(SUM(SalesStandard), 0) AS 'SalesTotal' FROM sales", connection);
Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: COALESCE + NULL in Ms SQL CE

Posted 07 November 2008 - 07:04 AM

what is the datatype of 'SalesStandard'? is it a numeric value or a string value?
Was This Post Helpful? 0
  • +
  • -

#6 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

Re: COALESCE + NULL in Ms SQL CE

Posted 11 November 2008 - 02:27 PM

Thanks solved it a typo :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1