Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Counting Items in Categories

    Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with

    Like

    Accommodation (10)
    Real Estate(30)
    Automotive(2)
    Education(1)....

    PHP Code:
    Select trade_category.iCategory_Name,Listing_category.iParentID,count(Listing_category.iCategoryID) as num 
    from Listing_category
    ,trade_category Where Listing_category.iParentID trade_category.iCategoryID  Group by
    Listing_category
    .iParentID,trade_category.iCategory_Name
    Union ALL
    Select Freecategory
    .sName,Listing_category.iParentID,count(Listing_category.iCategoryID) as num 
    from Listing_category
    ,Freecategory Where Listing_category.iParentID Freecategory.iFreeID  Group by
    Listing_category
    .iParentID,Freecategory.sName 
    Which Produces

    Real Estate 12401 12
    Extreme Sports 3 4

    I would Like to get the same query to produce a list of all the empty records too.
    so
    ID Count
    Accommodation 6112 0
    Real Estate 12401 12
    retail 12402 0
    Extreme Sports 3 4
    Cycling 5 0

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    There is no such concept of an 'empty record'. If you were to describe to me an 'empty record', what would it be? In situations similar to what you describe, a record can have one of the following characteristics:

    - Contain null values for one or more of its fields
    - Not be returned with respect to some given criteria.

    But there is no mention of an 'empty record' in relational theory or in any Database implementation. Looking at your query, I can't think of what it is you're actually trying to achieve, except in the case where a parent category may have no children, you need to return a result set similar to the following:

    {ParentID, ChildCount}
    ParentA, 0

    When viewed in this way, the problem becomes a trivial LEFT JOIN query that will return all rows from set A irrespective of the contents of set B. In your example however, instead of returning the rows from Set B you will just return a count of the rows.

    Code:
    Select
      SetA.columnA,
      count(SetB.columnA)
    from
      SetA
    
      left outer join SetB
      on SetA.ColumnA = SetB.ColumnB
    Remember: Simplification should be the goal of every developer. A paraphrased quote I once heard said: Perfection is reached not when you can no longer add to it, but when you can no longer take anything away.
    Last edited by r123456; 10-29-07 at 04:09.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Ive found a short term solution will look at speeding it up when I have some spare time, and I have the live version working so it makes a bit more sense.

    Start of December

Posting Permissions

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