Professional Web Applications Themes

SQL Counting Records from another Table - Microsoft SQL / MS SQL Server

Dear all sql fans, I have a query Table A catid catname 1 Fred 2 Ginger 3 Rogers 4 Dancing Table B id catid 1 2 2 2 3 3 4 2 Is it possible using sql to get the result Fred 0 Ginger 3 Rogers 1 Dancing 0 SQL counts gives (1's) instead if (0's) regards...

  1. #1

    Default SQL Counting Records from another Table

    Dear all sql fans, I have a query

    Table A
    catid catname
    1 Fred
    2 Ginger
    3 Rogers
    4 Dancing

    Table B
    id catid
    1 2
    2 2
    3 3
    4 2


    Is it possible using sql to get the result

    Fred 0
    Ginger 3
    Rogers 1
    Dancing 0

    SQL counts gives (1's) instead if (0's)

    regards
    nick Guest

  2. #2

    Default Re: SQL Counting Records from another Table

    DDL (CREATE TABLE) and sample data (INSERT) is typically much more useful
    than tabular structure and narrative. Also, I found it impossoble to mimic
    the exact order of your desired result set; it is not ordered alphabetically
    or numerically.





    CREATE TABLE TableA
    (
    catid INT,
    catname VARCHAR(32)
    )

    CREATE TABLE TableB
    (
    id INT,
    catid INT
    )

    SET NOCOUNT ON

    INSERT TableA VALUES(1, 'Fred')
    INSERT TableA VALUES(2, 'Ginger')
    INSERT TableA VALUES(3, 'Rogers')
    INSERT TableA VALUES(4, 'Dancing')

    INSERT TableB VALUES(1, 2)
    INSERT TableB VALUES(2, 2)
    INSERT TableB VALUES(3, 3)
    INSERT TableB VALUES(4, 2)

    SELECT
    a.catname,
    catcount = COALESCE(COUNT(b.id), 0)
    FROM TableA a LEFT JOIN TableB b
    ON a.catid = b.catid
    GROUP BY a.catname

    DROP TABLE TableA
    DROP TABLE TableB









    "nick" <com> wrote in message
    news:google.com... 


    Aaron Guest

  3. #3

    Default Re: SQL Counting Records from another Table

    Try:
    (untested)
    select catname,
    (select isnull(count(catid),0) from tableB where catid = a.catid) cnt
    from tablea a

    --
    -Vishal
    "nick" <com> wrote in message
    news:google.com... 


    Vishal Guest

  4. #4

    Default Re: SQL Counting Records from another Table

    The downside (IMHO) of using a subquery in this way is you get the
    following:

    Warning: Null value is eliminated by an aggregate or other SET operation.




    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 


    Aaron Guest

  5. #5

    Default Re: SQL Counting Records from another Table

    I think it will not because it will not find any matching rows.
    Check this example

    create table TableA(catid int, catname varchar(50))
    insert into TableA values (1 ,'Fred')
    insert into TableA values (2 ,'Ginger')
    insert into TableA values (3 ,'Rogers')
    insert into TableA values (4 ,'Dancing')

    create table TableB(id int, catid int)
    insert into Tableb values (1, 2)
    insert into Tableb values (2, 2)
    insert into Tableb values (3, 3)
    insert into Tableb values (4, 2)
    insert into Tableb values (4, NULL)

    SET ANSI_WARNINGS ON

    select catname,
    (select isnull(count(catid),0) from tableB where catid = a.catid) cnt
    from tablea a




    --
    -Vishal
    "Aaron Bertrand - MVP" <com> wrote in message
    news:#phx.gbl... 
    >
    >[/ref]


    Vishal Guest

  6. #6

    Default Re: SQL Counting Records from another Table

    You're absolutely right, the warning was actually coming from my query,
    which I ran first... Sorry! :-)


    "Vishal Parkar" <com> wrote in message
    news:eaJSX$phx.gbl... 


    Aaron Guest

  7. #7

    Default Re: SQL Counting Records from another Table

    no probs Aaron, :-)

    --
    -Vishal
    "Aaron Bertrand - MVP" <com> wrote in message
    news:#phx.gbl... 
    >
    >[/ref]


    Vishal Guest

Similar Threads

  1. SQL, counting records, two tables, the zero factor...
    By Thomas in forum Coldfusion - Getting Started
    Replies: 20
    Last Post: January 13th, 09:09 AM
  2. Counting records in query and dsiplaying! HELP
    By Moldie in forum Dreamweaver AppDev
    Replies: 2
    Last Post: July 19th, 05:25 PM
  3. SQL Server counting records
    By hoohim in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 26th, 12:05 PM
  4. Delete from one table with matching records in another table
    By Dale Fye in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 1st, 01:28 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