Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: Inserting a Count into a Table

    I am inserting data from one table into another. To simplify:


    Table 1 has columns:
    1.A, 1.B, 1.C, 1.D

    Table 2 has columns
    2.A, 2.B, 2.C, 2.D, 2.Tag

    where 2.Tag is a counter of all unique combinations of 1.C and 1.D.
    Not a count of how many records for each combination.

    Therefore, if Table 1 is

    x - x - x - x
    x - x - x - x
    x - x - x - x
    x - x - x - y
    x - x - x - z

    then, table 2 is

    x - x - x - x - 1
    x - x - x - y - 2
    x - x - x - z - 3



    anyone help me w/ the sql on that ?

    i was thinking of putting a variable in the select statement, but was unable to increment it.


    thanks

  2. #2
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    What do 2.A and 2.B hold? Is 1.A and 1.B relevant?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This could be done in a single select statements, but I refuse to think about it further until you explain why you would want to do such a loopy thing.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    Try this but I not sure my coding.
    INSERT INTO table1 SELECT DISTINCT *FROM table2

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a counter, not a count

    what sequence governs this counter, A and B?

    could you perhaps explain why you want this weird data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2003
    Posts
    357
    Try this

    Code:
    Declare @t table(A char(1), B char(1), C char(1), D Char(1))
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','y')
    insert into @t values('x','x','x','z')
    Select Distinct *,Tag=(Select count(*) from (Select Distinct D from @t) T1 where T1.D<=T2.D)  from @t T2
    Madhivanan

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    madhivanan, very nice try, but not quite right

    add the following to your test data and see what happens

    insert into @t values('x','x','y','y')
    insert into @t values('x','x','z','y')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2003
    Posts
    357
    r937

    try this with different combinations
    Code:
    Declare @t table(A char(1), B char(1), C char(1), D Char(1))
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','y')
    insert into @t values('x','x','x','z')
    insert into @t values('x','x','y','y')
    insert into @t values('x','x','z','y')
    
    Select Distinct *,Tag=(Select count(*) from (Select Distinct * from @t) T1 
    where  T1.A+T1.B+T1.C+T1.D<=T2.A+T2.B+T2.C+T2.D)  from @t T2
    Madhivanan
    Last edited by Madhivanan; 02-04-05 at 07:05.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, that's not right either

    add this to your data and see what happens --

    insert into @t values('y','y','x','x')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2003
    Posts
    357
    r937,

    Can you post the expected outcome for these data?

    Code:
    Declare @t table(A char(1), B char(1), C char(1), D Char(1))
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','y')
    insert into @t values('x','x','x','z')
    insert into @t values('x','x','y','y')
    insert into @t values('x','x','z','y')
    Madhivanan

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure

    x x x x 1
    x x x x 2
    x x x x 3
    x x x y 1
    x x x z 1
    x x y y 1
    x x z y 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2003
    Posts
    102
    Purpose of craziness: i want to order the data when i select from that table.

    for the below
    Declare @t table(A char(1), B char(1), C char(1), D Char(1))
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','x')
    insert into @t values('x','x','x','y')
    insert into @t values('x','x','x','z')
    insert into @t values('x','x','y','y')
    insert into @t values('x','x','z','y')


    the new table should have:

    x-x-x-x-1
    x-x-x-y-2
    x-x-z-y-3

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, I don't understand why your output isn't:

    x-x-x-x-1
    x-x-x-y-2
    x-x-x-z-3
    x-x-y-y-4
    x-x-z-y-5

    Second, if you are relying on the order of the data in the table for your logic, you are letting yourself in for a heap-o-hurtin'.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aargh, i think my understanding of this has been wrong all along

    it should be like in post #13!!

    okay, what about if you add these rows, then what do you get --

    insert into @t values('b','b','x','y')
    insert into @t values('c','c','x','z')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2003
    Posts
    102
    oh ya..
    1. the order should be that...(sorry, it is a friday)

    2. i want it ordered as such when i do an extract and display into a report.l



    thanks!
    C

Posting Permissions

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