Professional Web Applications Themes

Count the number of items from a subquery - Microsoft SQL / MS SQL Server

Hi, I have two tables I'm using-- "folders" and "folderitems". Think of this like Windows Explorer, where you have folders and excel, word docs, or whatever contained within them. The two tables are linked on the "FolderID" key using a one-to-many relationship. What I'm trying to run is a query which gives me the folder names and the # of folderitems contained within. Something like this: folder name number of items Folder1 33 Folder77 2377 I'm trying to visualize the query to run this, but I'm not too good at subqueries (which I'd assume you'd need to get the count), ...

Sponsored Links
  1. #1

    Default Count the number of items from a subquery

    Hi,

    I have two tables I'm using-- "folders" and "folderitems". Think of
    this like Windows Explorer, where you have folders and excel, word docs, or
    whatever contained within them.

    The two tables are linked on the "FolderID" key using a one-to-many
    relationship.

    What I'm trying to run is a query which gives me the folder names and
    the # of folderitems contained within. Something like this:

    folder name number of items

    Folder1 33
    Folder77 2377

    I'm trying to visualize the query to run this, but I'm not too good at
    subqueries (which I'd assume you'd need to get the count), and was wondering
    if someone could point me in the right direction. Thanks.




    Sponsored Links
    Jim Guest

  2. #2

    Default Count the number of items from a subquery

    This is a correlated subquery that does the job

    Select FolderName,
    NumberOfItems=(select count(*) FROM FolderItems fi
    WHERE fi.folderid=f.folderid)
    FROM Folder f

     
    and "folderitems". Think of 
    excel, word docs, or 
    using a one-to-many 
    folder names and 
    this: 
    I'm not too good at 
    count), and was wondering 
    Thanks. 
    laurent Guest

  3. #3

    Default Re: Count the number of items from a subquery

    Works great-- thanks!


    Jim Guest

Similar Threads

  1. Ben Forta - Where are you?...How to Count items in aSession Variable
    By smokin_joe in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: June 9th, 06:48 PM
  2. Items.Count wrong on partial page
    By Paul in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: July 15th, 04:01 AM
  3. PageCount too high for Items.Count
    By Keith Patrick in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: June 9th, 03:48 PM
  4. Handler error in items.count
    By eggler in forum Macromedia Director Basics
    Replies: 0
    Last Post: October 27th, 06:17 AM
  5. Count two distinct items in a table.
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 16th, 04:57 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139