Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    9

    Unanswered: Set value to 0 if does not exist

    Hi,

    Can I set a value to zero on a count such as:


    SELECT appold.build_name, appold.CAT, COUNT(appold.CAT) AS CountofCAT1 FROM appold
    WHERE (appold.monthentry = MMColParam)
    GROUP BY appold.build_name, appold.CAT

    there are 3 types that CAT can be 1,2 or 3 but sometimes a build_name may not have one or more CAT, but I would like it to be 0 if it does not exist.

    Regards,

    Sanjay

  2. #2
    Join Date
    Aug 2003
    Posts
    34

    Lightbulb

    Count is just Counter to the records that meet the conditions , so it is output , so you can not set a value to it, it will count and tell you the result, so in your case if there is no records that match the criterial, it's value will be zero.
    i hope i understand your problem.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need another table (lets call it CATS) that has one (at least) field called CAT and three records: 1, 2, and 3.

    Next, create a query called CATbuildname that contains all possible distinct CAT/buildname combinations:

    SELECT DISTINCT appold.buildname, CATS.CAT FROM CATS, appold;

    Next create a query that filters your appbuild for MMColParam records. Call it MMColParambuilds:

    SELECT appold.buildname, appold.CAT, appold.monthentry
    FROM appold
    WHERE appold.monthentry=[MMColParam];

    Finall left join your MMColParambuilds query to the CATbuildnames query and perform your count:

    SELECT CATbuildnames.buildname, CATbuildnames.CAT,
    Count(MMColParambuilds.monthentry) AS CATCount
    FROM CATbuildnames
    LEFT JOIN MMColParambuilds
    ON (CATbuildnames.CAT = MMColParambuilds.CAT)
    AND (CATbuildnames.buildname = MMColParambuilds.buildname)
    GROUP BY CATbuildnames.buildname, CATbuildnames.CAT;

    Voila!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2003
    Posts
    9
    Thanks Blindman,

    I have created a recordset as you specified, but I'm using Dreamweaver but am not sure how to implement the recordset together,

    I created a table with CAT1, CAT2 and SR called CATS

    Is that right?

    Regards,

    Sanjay

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In SQL Server the whole thing can be written as a single SQL statement with subqueries. It should be possible to do this in Access as well, though I couldn't get the subqueries to work correctly for some reason.

    I don't know anything about Dream Weaver, and what is SR?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2003
    Posts
    9
    Hi Blindman,

    What I have is for each building (build_name) there can be be CAT1, CAT2 or SR value but I would like to return that value even though it may not exist, which I think your first query has,

    so with my oringinal query:

    SELECT appold.build_name, appold.CAT, COUNT(appold.CAT) AS CountofCAT1 FROM appold
    WHERE (appold.monthentry = MMColParam)
    GROUP BY appold.build_name, appold.CAT

    (the MMColParam is a querystring that is requested from the ASP )

    I retreived:

    building1 CAT1 12
    building1 CAT2 24
    building2 CAT1 3
    building2 CAT2 1
    building2 CATSR 8

    etc

    so it skips building1 CATSR as there is no record for it,

    in your first query, should I have created a table (CATS) with each building and CAT type i.e.

    building1 CAT1
    building1 CAT2
    building1 CATSR
    building2 CAT1
    building2 CAT2
    building2 CATSR
    etc.

    Regards,

    Sanjay

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. If you have a table will all three CAT types (CAT1, CAT2, anc CATSR) and then do a distinct cross join with the building field in the appold table it will create a virtual result set of all the combinations of buildings and CAT types. This is better, because you won't have to update it if you add or delete buildings from your database. Link the results of this query as a subquery in your main query, as I explained in the first post.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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