Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Unanswered: Output Returning Multiple Lines

    I am running this stored Prcedure and getting multiple lines for the output. Below are the queries the create the temp table (its holding the data), and the query that generates the output:

    /* this creates the temp table */
    Select count(*) as 'Donations', d_vst_id
    into Donations_temp
    from dnr_vst_db_rec
    where convert(varchar(10),d_vst_date) between convert(varchar(10), @Beg_Vst_Date, 112) and convert(varchar(10), @End_Vst_Date, 112)
    and d_vst_status = 'DN'
    and d_vst_dontyp in ('E1', 'E2')
    group by d_vst_id

    /* this query generates the output */

    select distinct cast(getdate() as varchar(30)) as 'TODAY'
    ,CONVERT(varchar(10), @Beg_Vst_Date,101) as 'BEGDTE'
    ,CONVERT(varchar(10), @End_Vst_Date,101) as 'ENDDTE'
    ,case Donations
    when '1' then sum(1)
    else 0
    end as 'ONE1'
    ,case Donations
    when '2' then sum(1)
    else 0
    end as 'ONE2'
    ,case Donations
    when '3' then sum(1)
    else 0
    end as 'ONE3'
    ,case Donations
    when '4' then sum(1)
    else 0
    end as 'ONE4'
    ,case Donations
    when '5' then sum(1)
    else 0
    end as 'ONE5'
    ,case Donations
    when '6' then sum(1)
    else 0
    end as 'ONE6'
    ,case Donations
    when '7' then sum(1)
    else 0
    end as 'ONE7'
    ,case Donations
    when '1' then Sum(0)
    when '2' then Sum(0)
    when '3' then Sum(0)
    when '4' then Sum(0)
    when '5' then Sum(0)
    when '6' then Sum(0)
    when '7' then Sum(0)
    else Sum(1)
    end as 'ONEA'
    from Donations_temp
    group by Donations

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, you are grouping the the Donations field, so if you will get one record for each distinct Donation value. If you want one record across the entire set then omit the Group By clause.

    Second, I don't think you are going to get the results you want from statements like:

    "case Donations when '1' then sum(1) else 0 end as 'ONE1',"

    ...they should probably be rephrased like this:

    "sum(case Donations when '1' then 1 else 0 end case) as 'ONE1',"

    Distinct sum(1) is not going to give you a count. It should always return "1".

    Lastly, the Distinct clause is not necessary in an aggregate query. The results are always distinct against the field in the Group By clause. If you omit the Group By clause altogether, then you will get a single distinct record as a result.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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