Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Unanswered: how to add column values?

    I've got a view that has calculated columns, and I need to add another column that gives a total sum of the values in each row.. .. so ColumnA + ColumnB + ColumnC etc... .

    Part of my query looks like this:
    SELECT 1 AS ResultOrder, 'TotalScreen' AS TotalName, SUM(CASE WHEN Site = '01' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column A], SUM(CASE WHEN Site = '02' THEN CASE WHEN DateAsked= dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column B], SUM(CASE WHEN Site = '03' THEN CASE WHEN DateAsked= dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column C], '' AS 'Across Sites'

    In that last column that I called 'Across Sites', I want to sum up the values of ColumnA, ColumnB, and ColumnC. but how do I reference them?

    When I put ([ColumnA] + [ColumnB] + [ColumnC]) AS 'Across Sites', I get an error.

    Help please?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ResultOrder
         , TotalName
         , [Column A]
         , [Column B]
         , [Column C]
         , [Column A] + [Column B] + [Column C] AS 'Across Sites'
      FROM ( SELECT 1 AS ResultOrder
                  , 'TotalScreen' AS TotalName
                  , SUM(CASE WHEN Site = '01' 
                              AND DateAsked = dateadd(ms,0,CONVERT(varchar,GetDate(),101)) 
                             THEN 1 ELSE 0 END) AS [Column A]
                  , SUM(CASE WHEN Site = '02' 
                              AND DateAsked = dateadd(ms,0,CONVERT(varchar,GetDate(),101)) 
                             THEN 1 ELSE 0 END) AS [Column B]
                  , SUM(CASE WHEN Site = '03' 
                              AND DateAsked = dateadd(ms,0,CONVERT(varchar,GetDate(),101)) 
                             THEN 1 ELSE 0 END) AS [Column C]
               FROM ... ) AS t
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    sharc, another possibility (although I like r937's solution better (easier to read and understand)) is:
    Code:
    SELECT 1 AS ResultOrder
         , 'TotalScreen' AS TotalName
         , SUM(CASE WHEN Site = '01' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column A]
         , SUM(CASE WHEN Site = '02' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column B]
         , SUM(CASE WHEN Site = '03' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Column C]
    
         , SUM(CASE WHEN Site = '01' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END)
         + SUM(CASE WHEN Site = '02' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END)
         + SUM(CASE WHEN Site = '03' THEN CASE WHEN DateAsked = dateadd(ms, 0, CONVERT(varchar, GetDate(), 101)) THEN 1 ELSE 0 END ELSE 0 END) AS [Across Sites]

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What is this about?

    Code:
    DateAsked=dateadd(ms,0,CONVERT(varchar,GetDate(),101))
    You are explicitly converting the datetime to a varchar, then implicitly converting it back to a datetime to add zero milliseconds to it, resulting in a datetime value, to then compare it to the DateAsked column (which we are not sure if it is a string or datetime.

    Why not just:

    Code:
    DateAsked=CONVERT(date,GetDate())
    I'm just a beginner here, so why all the convoluted conversion? Is there a valid reason?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jul 2007
    Posts
    4
    Thanks a lot :-) Both suggestions are great, and I'll definately never forget this from now on!!

    Ken - with regards to why that was written in that form, I couldn't say. . . I "inherited" the script, and really didn't even glance that far down the link to see what tweaks could be made; I was just sort of concentrating on the task that was giving me headaches at the time, lol. But I'll certainly give another look as soon as I check off my todo list.

    Thanks again everyone!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PracticalProgram View Post
    Why not just:

    Code:
    DateAsked=CONVERT(date,GetDate())
    because that won't work in versions before SQL Server 2008

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well, that's somewhat by the by. Are you saying that CONVERT(DATETIME, GETDATE()) would make sense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    Are you saying that CONVERT(DATETIME, GETDATE()) would make sense?
    are you asking me?

    i would say no, it wouldn't make sense, on two counts -- first, it isn't necessary to convert GetDate() to datetime, because it already is, but more importantly, this would not strip the time off to make it midnight
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh - I missened that bit
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    r937

    I don't have a copy of SQL prior to SQL2008 up and running, but would

    Code:
    DateAsked=CAST(getdate() as date)
    have worked prior to SQL2008?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No- DATE is a new data type in SQL 2008 R1.

    Standard technique for stripping time off a date (prior to 2008):
    Code:
    SELECT  DATEADD(d, DATEDIFF(d, '19000101', GETDATE()), '19000101')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's my preferred method pre-2008 too Poots
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I even included The Phelan Placation Parameter (or Triple P).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    I even included The Phelan Placation Parameter (or Triple P).
    uff da!

    is this related to the use of '19000101'?

    i too like that a whole lot better than the simple 0 which you usually see in that strip time expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PracticalProgram View Post
    I don't have a copy of SQL prior to SQL2008 up and running...
    [ offtopic rant follows ]

    i don't have ~any~ copy of SQL Server on my computer any more

    i had been running Express for, like, ever since they made it available, but recently (in my dotage) i just got too tired of all the reinstalls

    microsoft software is user-hostile, and that's all i am, a user, and i refuse to hire a DBA who knows what a named pipe is just for the vaunted privilege of using software that's usually as slow as cold glue

    i trashed oracle years ago for the same reason only it was way more acute, i never got it installed successfully

    in comparison, mysql has been as simple to use as Windows Paint (but i must confess i am still on version 4.1)

    mysql, plus a bootleg copy of Access 95, are the only remaining database systems in my arsenal




    heh, i said arsenal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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