Professional Web Applications Themes

return rows when Item not in source data? - Microsoft SQL / MS SQL Server

Hello, Here is my source data table (tbl1) Item Month Num A Jan 3 B Jan 2 C Jan 1 A Jan 6 B Jan 11 C Jan 8 B Feb 4 B Feb 6 A Mar 7 C Mar 9 A Mar 12 If I count this data by Item by month, A and C will not show up for Feb. I need A and C to show up for Feb with a count of 0. I tried creating a list table (tblABC) containing all the Items in a unique field called "Item" and then doing a count by ...

  1. #1

    Default return rows when Item not in source data?

    Hello,

    Here is my source data table (tbl1)
    Item Month Num
    A Jan 3
    B Jan 2
    C Jan 1
    A Jan 6
    B Jan 11
    C Jan 8
    B Feb 4
    B Feb 6
    A Mar 7
    C Mar 9
    A Mar 12

    If I count this data by Item by month, A and C will not show up for Feb.
    I need A and C to show up for Feb with a count of 0.

    I tried creating a list table (tblABC) containing all the Items in a
    unique field called "Item" and then doing a count by joining tbl1 to
    tblABC on the Item field. Items A and C still did not show up with a
    count of 0 for Feb. I want to include all Rows in tblABC for all months
    in tbl1. I tried Right Join, still nothing.

    Select t1.Item, Sum(t1.num) from tbl1 t1 Join tblABC Group by Item,
    Sum(t1.num)

    Request any suggestions how to achieve returning all Items wether 0 or
    not.

    Thanks,

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Rich Guest

  2. #2

    Default Re: return rows when Item not in source data?

    Here is one...

    create table t1(Item char, Mon varchar(3), Num int)

    insert t1 select 'A', 'Jan' , 3
    union all select 'B', 'Jan' , 2
    union all select 'C', 'Jan' , 1
    union all select 'A', 'Jan' , 6
    union all select 'B', 'Jan', 11
    union all select 'C', 'Jan' , 8
    union all select 'B', 'Feb', 4
    union all select 'B', 'Feb' , 6
    union all select 'A', 'Mar' , 7
    union all select 'C', 'Mar' , 9
    union all select 'A', 'Mar', 12
    go

    select t2.Item,t3.Mon,count(t1.Item) cnt
    from t1 right join (select distinct Item from t1)t2 cross join (select distinct Mon from t1)t3 on t1.Item=t2.Item and t1.Mon=t3.Mon
    group by t2.Item,t3.Mon
    go

    drop table t1
    go



    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Rich Protzel" <com> wrote in message news:#phx.gbl...
    Hello,

    Here is my source data table (tbl1)
    Item Month Num
    A Jan 3
    B Jan 2
    C Jan 1
    A Jan 6
    B Jan 11
    C Jan 8
    B Feb 4
    B Feb 6
    A Mar 7
    C Mar 9
    A Mar 12

    If I count this data by Item by month, A and C will not show up for Feb.
    I need A and C to show up for Feb with a count of 0.

    I tried creating a list table (tblABC) containing all the Items in a
    unique field called "Item" and then doing a count by joining tbl1 to
    tblABC on the Item field. Items A and C still did not show up with a
    count of 0 for Feb. I want to include all Rows in tblABC for all months
    in tbl1. I tried Right Join, still nothing.

    Select t1.Item, Sum(t1.num) from tbl1 t1 Join tblABC Group by Item,
    Sum(t1.num)

    Request any suggestions how to achieve returning all Items wether 0 or
    not.

    Thanks,

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!


    oj Guest

  3. #3

    Default Re: return rows when Item not in source data?

    here it is before you gonna ask for the totals...

    select t2.Item,t3.Mon,sum(isnull(t1.Num,0)) Totals
    from t1 right join (select distinct Item from t1)t2 cross join (select distinct Mon from t1)t3 on t1.Item=t2.Item and t1.Mon=t3.Mon
    group by t2.Item,t3.Mon


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Rich Protzel" <com> wrote in message news:#phx.gbl...
    Hello,

    Here is my source data table (tbl1)
    Item Month Num
    A Jan 3
    B Jan 2
    C Jan 1
    A Jan 6
    B Jan 11
    C Jan 8
    B Feb 4
    B Feb 6
    A Mar 7
    C Mar 9
    A Mar 12

    If I count this data by Item by month, A and C will not show up for Feb.
    I need A and C to show up for Feb with a count of 0.

    I tried creating a list table (tblABC) containing all the Items in a
    unique field called "Item" and then doing a count by joining tbl1 to
    tblABC on the Item field. Items A and C still did not show up with a
    count of 0 for Feb. I want to include all Rows in tblABC for all months
    in tbl1. I tried Right Join, still nothing.

    Select t1.Item, Sum(t1.num) from tbl1 t1 Join tblABC Group by Item,
    Sum(t1.num)

    Request any suggestions how to achieve returning all Items wether 0 or
    not.

    Thanks,

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!


    oj Guest

  4. #4

    Default Re: return rows when Item not in source data?

    Wow! This is great! Thanks so much for sharing. Everythign works just
    as advertised.

    Many thanks!

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rich Guest

  5. #5

    Default Re: return rows when Item not in source data?

    Hi OJ,

    You are the man! Here is my actual (convoluted query)

    Declare Mon nvarchar(50)
    Declare ReqType nvarchar(50)
    Set Mon = 'aJan'
    Set ReqType = 'Urgent'
    SELECT
    t4.[MemSA_1],
    t4.Mnth,
    Count(t1.[Member Facility]) AS [TransportCount]
    FROM tblzDonAmbN t1 right join(select distinct t2.MemSA_1,t3.Mnth from
    tblzdonAmbN t2
    cross join tblzDonAmbN t3)t4 On t1.MemSA_1 = t4.MemSA_1 And t1.Mnth =
    t4.Mnth
    AND t1.ReqType_1 = ReqType
    AND (t1.PsychYesNo = 1)
    --AND (t1.MemSA_1 In ('Capital','Central California','East Bay','Golden
    Gate','Northeast Bay','South Bay'))
    WHERE
    t4.Mnth = Mon
    AND (t4.MemSA_1 In ('Capital','Central California','East Bay','Golden
    Gate','Northeast Bay','South Bay'))
    GROUP BY t4.MemSA_1, t4.Mnth

    -----and here is my result set:
    MemSA Mnth TransportCount
    Capital aJan 0
    Central California aJan 0
    East Bay aJan 0
    Golden Gate aJan 2
    Northeast Bay aJan 3
    South Bay aJan 0

    Note: your query was originally picking up extraneous MemSA's, so I
    first added an In clause before the where statement. That did not get
    rid of the extraneous MemSA's. So then I decided to add the In clause
    after the Where clause with t4.MemSA instead of t1. That did the trick.
    Also, I prefixed my months with letters like aJan, bFeb, cMar, for
    ordering. I actually have a Date field, but I added the Mnth column to
    simplify the querying and ordering, don't have to use Date between ...

    This is way better than what I was going to do, which was to retrieve my
    count of MemSA's, then check RowCount, if less than 6 (I am interested
    in a specific 6 MemSA's) then I insert the remaining MemSA's with count
    of 0. Well, that is my backup solution. I just hope I can incorporate
    your solution to the rest of my project because each MemSA contains
    several Facilities. I have to do the same thing by Facility next (my
    data sets contain like 3,000,000+ records). Eventually, all the queries
    will be in sp's which I will call from ADO.

    Many many thanks for all your help.

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rich Guest

  6. #6

    Default Re: return rows when Item not in source data?

    Hi OJ,

    Hope you are still there :) *** :(. That Facility query I mentioned
    uses a separate table listing distinct facilities to count. I thought
    it would be a snap to get rows for a zero count of facilities for a
    given month, especially using your technique, but my brain just isn't
    sophisticated enough, and thus, I respectfully/humbly request if you
    could shed just one more ray of your wisdom my way. Here is my query.
    There are 5 Facilities in the Capital MemSA in tbl2 (tbl2 contains a
    list of 5 specific facilites of interest for each of the 6 MemSA's of
    interest). This query yields nothing, but I would like 5 rows with each
    of the facils in Capital with a count of 0.

    Declare Mon varchar(5)
    Declare ReqType varchar(10)
    Declare MemSa varchar(20)
    Set Mon = 'aJan'
    Set ReqType = 'Urgent'
    Set MemSa = 'Capital'
    SELECT
    t1.[MemSA_1],
    t1.[Member Facility],
    Count(t1.[Member Facility]) AS [TransportCount],
    FROM tbl1 t1 Join tbl2 t2 On
    (t1.[MemFacil] = t2.[MemFacil])
    And (t1.MemSA = t2.MemSA)
    WHERE
    t1.Mnth = Mon
    AND (t1.MemSA = MemSA)
    AND (t1.PsychYesNo = 1)
    AND (t1.[ReqType_1] = 'Urgent')
    GROUP BY t1.[MemSA] , t1.[MemFacil]
    ORDER BY t1.[MemFacil]

    What I tried was this
    (tbl1 t1 join tbl2 t2 On t1.MemFacil = t2.MemFacil)t3 Righ Join
    (t4.Memfacil tbl2 t4) On t3.MemFacil = t4.MemFacil

    but since tbl2 is separate from tbl1 I did not see a cross join here.
    Anyway, I was getting syntax error after syntax error. May I request if
    you could shed light on this one more time? The alternative is to do
    spaghetti code (I'm all of of sauce!).

    Thanks for all your help you have given me already.
    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rich Guest

  7. #7

    Default Re: return rows when Item not in source data?

    OK. I added a few more fields to my practice query in
    Access and came up with the same problem I am having in
    Sql Server where not returning any records. In tbl2 of my
    example I added one more field Req
     

    If I use this query I do not get any rows;

    SELECT Count(t2.facil) AS cntFacil, t1.facil, t2.mnth,
    t2.memsa, t1.memsa, t2.req
    FROM tbl1 LEFT JOIN tbl2 ON (t1.memsa = t2.memsa) AND
    (t1.facil = t2.facil)
    GROUP BY t1.facil, t2.mnth, t2.memsa, t1.memsa, t2.req
    HAVING (t2.req="b")

    It looks like I may have to artificially inseminate my
    table with 0 row counts for my distinct facils from tbl1.
    Would a crossjoin to tbl1 be a trick to use here?

    Thanks,
    Rich
     
    from 
    >[MemFacil]) And 
    >the ddl (create [/ref]
    try [/ref]
    >query I mentioned [/ref]
    >count. I thought [/ref]
    >facilities for a [/ref]
    >brain just isn't [/ref]
    >request if you [/ref]
    >Here is my query. [/ref]
    >(tbl2 contains a [/ref]
    >the 6 MemSA's of [/ref][/ref]
    like [/ref]
    >Righ Join [/ref]
    >cross join here. [/ref][/ref]
    error. [/ref]
    >alternative is to do [/ref][/ref]
    http://www.developersdex.com 
    >>
    >>
    >>.
    >>[/ref]
    >.
    >[/ref]
    Rich Guest

  8. #8

    Default Re: return rows when Item not in source data?

    When it comes to perfomance tuning, everything is relative. It all depends
    on the table schema, indexes, and the amount of data. In general, set based
    solutions work better than procedural solutions (i.e. cursor, while loop,
    etc.). However, not all set based solutions are the same - One can write
    many inefficient queries that do produce the same result.

    Here are a few tips in your quest for *higher* tsql knowledge <G>

    1. When posting to newsgroup, you want to post ddl (create table) + sample
    data (insert) + expected result. You can use one of our free tools,
    ObjectScriptr/QALite, for this.

    2. For a clean test between each run/batch/query, you want to clean all
    cache.

    e.g.
    set statistics io on
    set statistics time on
    go
    dbcc freeproccache
    dbcc dropcleanbuffers
    checkpoint
    go
    <query #1>
    go
    dbcc freeproccache
    dbcc dropcleanbuffers
    checkpoint
    go
    <query #2>
    go
    set statistics io off
    set statistics time off
    go

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Rich" <com> wrote in message
    news:0e1501c3672e$6c96e210$gbl... 

    > this. 
    > AND [/ref]
    > my [/ref]
    > tbl1. [/ref]
    > Access [/ref]
    > more [/ref]
    > t2. [/ref]
    > post [/ref]
    > respectfully/humbly [/ref]
    > t3 [/ref]
    > it! 
    > >
    > >
    > >.
    > >[/ref][/ref]


    oj Guest

  9. #9

    Default Re: return rows when Item not in source data?

    Wow! This is great! I was not able to find

    dbcc dropcleanbuffers

    in the Books online or in my textbook. I imagine some
    things I will just get from hanging out in the NG's.

    I am one of those kind of people that if I read about a
    subject I can sort of understand it but won't retain a lot
    until I get the hands on. Well, my sql classes all had
    win2k Advanced Server with sql7 (back a few years). But I
    was not working with it at work. Now I am. Now when I re-
    read stuff it should stick.

    Well, thanks for all the wisdom and enlightenment you have
    shared with me thus far. I will be digesting it all for a
    while, but I am sure I will be posting more in the near
    future. In the meantime, I am putting on my bib and start
    digesting :).

    Rich
     
    relative. It all depends 
    general, set based 
    cursor, while loop, 
    One can write 
    knowledge <G> 
    (create table) + sample 
    free tools, 
    want to clean all [/ref]
    them - [/ref]
    to 
    >> 
    >> this. 
    >> AND [/ref][/ref]
    in [/ref][/ref]
    in [/ref][/ref]
    of [/ref][/ref]
    t2.mnth, [/ref][/ref]
    t2.req [/ref][/ref]
    my 
    >> tbl1. [/ref][/ref]
    twice [/ref][/ref]
    more 
    >> Access [/ref][/ref]
    ON [/ref][/ref]
    memsa. 
    >> more [/ref][/ref]
    Server 
    >> t2. 
    >> post [/ref][/ref]
    Will [/ref][/ref]
    Facility [/ref][/ref]
    facilities to [/ref][/ref]
    of [/ref][/ref]
    but my 
    >> respectfully/humbly [/ref][/ref]
    way. [/ref][/ref]
    tbl2 [/ref][/ref]
    each of [/ref][/ref]
    would [/ref][/ref]
    t2.MemFacil) [/ref][/ref]
    t4.MemFacil [/ref][/ref]
    see a [/ref][/ref]
    already. [/ref][/ref]
    for [/ref]
    >
    >
    >.
    >[/ref]
    Rich Guest

Similar Threads

  1. Return count() with the rows
    By pengypenguin@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 14th, 04:15 PM
  2. Return more than one item with an ASMX page
    By USCG in forum ASP.NET Web Services
    Replies: 1
    Last Post: March 11th, 01:08 PM
  3. how to return all rows selected in subroutine?
    By Shaunn Johnson in forum PERL Beginners
    Replies: 1
    Last Post: August 29th, 08:33 PM
  4. Library item as external source file
    By Murray in forum Macromedia Dreamweaver
    Replies: 4
    Last Post: July 30th, 11:10 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