Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Question Unanswered: concatenating null in a SQL?

    I try to count a number which is a string, but some rows are empty (null) so I need to make some thing to make this work.
    Any ideas?
    Thanks//Martin

    SQL1 = "SELECT DISTINCT COUNT(cint(Fnr)) AS AntalUnika FROM plan_info"
    "Never underestimate a large number of morons"

  2. #2
    Join Date
    May 2004
    Posts
    125
    If I understand this, you have multiple rows but the values you are using to count on are equal to null.

    Try doing this:

    SQL1 = "SELECT DISTINCT COUNT(1) AS AntalUnika FROM plan_info"

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    yes its a table with a field which is in some rows null, not all of them, most of the rows have a number as a string for example "12332455" but there are duplicates so I have to use the distinct..Maby I can use some kind of case statement?
    The table is actually a "viritual acess table" wich contents is dependent on real tables in the mdb, so I can´t edit the fields properties to not allow nulls either
    //M
    Last edited by Td04; 09-29-04 at 15:42.
    "Never underestimate a large number of morons"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, there are several things wrong here

    if you are doing a count from a table, then DISTINCT is not necessary, because the aggregate function will return only one value, and it will be distinct

    if you are just counting them, there's no need to convert them from string to integer

    if all you want is the number of non-nulls, then just count the column values, because aggregate functions ignore nulls

    if you want the count of the number of distinct values, then the DISTINCT has to go inside the COUNT()

    so, what do you actually want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    52
    ok I thought i had to use a numeric field to do the count.. so count skipp the "nulls" thats good.
    What I would like to do is count the records in a previous selection.
    But only to count the Unique values, so the duplicate values does not get included.
    I dont want to count the whole table, but only a user selected part so its kind of tricky. :-#

    'first sql
    cn="Driver=Microsoft Access Driver (*.mdb); DBQ=Q:\Winapp32\Edp\Fasoff\data\fas.mdb;"
    set rs = server.createobject("ADODB.Recordset")
    SQL = "SELECT * FROM plan_info WHERE LMAKT = '"& session("planen") &"' order by FastBeteckn"
    rs.open SQL,cn
    'number of unique
    Set rs1 = Server.CreateObject("ADODB.Recordset")
    SQL1 = "SELECT COUNT(Fnr) AS AntalUnika FROM plan_info"
    rs1.Open SQL1,cn
    Last edited by Td04; 09-30-04 at 03:58.
    "Never underestimate a large number of morons"

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select count(distinct Fnr) as AntalUnika 
      from plan_info
     where LMAKT = value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    52
    That looks promising but..
    I tried as below but I get syntax error at this line
    "Syntaxerror (missing operator) in experssion 'count(distinct Fnr)'.
    did I miss out on sth?
    This is the code I tried
    thanks//M

    'user query
    cn="Driver=Microsoft Access Driver (*.mdb); DBQ=Q:\Winapp32\Edp\Fasoff\data\fas.mdb;"
    set rs = server.createobject("ADODB.Recordset")
    'SQL = "SELECT FastBeteckn,NAMN,PLNAMN,LMAKT,PLANFK,PSTAT,PLANANM M FROM plan_info WHERE LMAKT = '"& session("planen") &"' order by FastBeteckn"
    SQL = "SELECT * FROM plan_info WHERE LMAKT = '"& session("planen") &"' order by FastBeteckn"
    rs.open SQL,cn
    'number of unique
    Set rs1 = Server.CreateObject("ADODB.Recordset")
    SQL1 = "select count(distinct Fnr) as AntalUnika from plan_info where LMAKT = value"
    rs1.Open SQL1,cn
    "Never underestimate a large number of morons"

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i knew this but i momentarily forgot it

    access can't do COUNT(DISTINCT xxx))

    what you could do is run SELECT DISTINCT Fnr .... and then use RecordCount
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2004
    Posts
    52
    ok, "r937", "DMWCincy" Thanks alot your help, I really have to get a good SQL book one of these days..
    //Martin
    "Never underestimate a large number of morons"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •