Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Total Column Before GROUP BY

Answered Total Column Before GROUP BY

  • Tuesday, January 29, 2013 12:44 PM
     
      Has Code
    Sign In to Vote
    0

    I'm using Full-Text Search to search an historical table.
    I'd like to show only records that have an EVENT_TYPE of Open (by grouping ID)

    My example data from the search query (inserted into a temp table):

    ID    | EVENT_TYPE| EVENT_DATE | COMMENTS |   MATCH
    12345     Closed    2009-01-09        upd Text          47
    12345     Open      2009-01-06        opn Text          33
    12345     Update    2009-01-07       upd Text          12
    12345     Update    2009-01-07       upd Text          22
    76543     Closed    2008-05-11        upd Text          12
    76543     Update    2008-05-10       upd Text          80
    76543     Open      2008-05-08        opn Text          21
    76543     Update    2008-05-09       upd Text          08


    My ideal target data from the above:

    ID    | EVENT_TYPE  | EVENT_DATE |  COMMENTS | MATCH
    12345     Open          2009-01-06        opn Text        114
    76543     Open          2008-05-08        opn Text        121

    How do I total the match column for the ID before grouping? ( While selecting only the EVENT_DATE and COMMENTS from the record with the EVENT_OPEN = 'Open')

    SELECT ID, EVENT_TYPE, EVENT_DATE, COMMENTS, MATCH 
FROM #TempTable
WHERE EVENT_TYPE = 'Open'
GROUP BY ID, EVENT_TYPE, EVENT_DATE, COMMENTS, MATCH
ORDER BY MATCH

     

All Replies

  • Tuesday, January 29, 2013 12:56 PM
     
      Has Code
    Sign In to Vote
    0

    If my under standing is correct then the below query will give u appropriate result.

    SELECT ID, 
	(select EVENT_TYPE from #TempTable where Tp.ID=ID and Tp.EVENT_DATE=EVENT_DATE) AS EVENT_TYPE, 
	min(EVENT_DATE) as EVENT_DATE, 
	(select COMMENTS from #TempTable where Tp.ID=ID and Tp.EVENT_DATE=EVENT_DATE) AS COMMENTS, 
	sum(MATCH) as MATCH 
FROM #TempTable Tp
WHERE EVENT_TYPE = 'Open'
GROUP BY ID
ORDER BY MATCH


    sarat chandra sahoo

     
  • Tuesday, January 29, 2013 12:58 PM
     
     Answered
    Sign In to Vote
    0

    Something like this:

    ;

    WITH cte as

    (

    SELECT ID, SUM(MATCH) as MatchTotal

    FROM

    #TempTable

    GROUP

    BY ID)

    SELECT

    t.ID, EVENT_TYPE, EVENT_DATE, COMMENTS, cte.MatchTotal

    FROM

    #TempTable t

    JOIN

    cte ON t.id = cte.id

    WHERE

    t.EVENT_TYPE = 'Open'

    GROUP

    BY t.ID, EVENT_TYPE, EVENT_DATE, COMMENTS, cte.MatchTotal

    ORDER

    BY cte.MatchTotal

    Chuck Pedretti | Magenic – North Region | magenic.com

    　

    　


    • Edited by Chuck Pedretti Tuesday, January 29, 2013 1:01 PM
    • Marked As Answer by madlan Tuesday, January 29, 2013 1:21 PM
    •  
     
  • Tuesday, January 29, 2013 12:59 PM
    Answerer
     
      Has Code
    Sign In to Vote
    0

    >>>How do I total the match column for the ID before grouping? (

    What does it mean?

    Sorry, cannot test it right now

    SELECT ID, EVENT_TYPE, EVENT_DATE, COMMENTS, SUM(MATCH) OVER (PARTITION BY ID) TOTAL
FROM #TempTable
WHERE EVENT_TYPE = 'Open'
ORDER BY MATCH

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Tuesday, January 29, 2013 1:01 PM
     
      Has Code
    Sign In to Vote
    0

    Your best bet here is probably a window function, such as 

    SUM(MATCH) OVER(PARTITION BY ID)
    This will return the total across your partition field without aggregating the table.  However if you then want to group by the field you'll need to put this into an inner query/cte then aggregate in an outer query.

     
  • Tuesday, January 29, 2013 1:11 PM
     
     
    Sign In to Vote
    0

    I meant total MATCH column of the records with the same ID.

    Your query does not total (SUM) the MATCH column? It just returns the 'Open' MATCH value.

     
  • Tuesday, January 29, 2013 1:15 PM
     
     
    Sign In to Vote
    0

    I meant total MATCH column of the records with the same ID.

    Your query does not total (SUM) the MATCH column? It just returns the 'Open' MATCH value.


    I believe that my query returns the result you are interested in

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Tuesday, January 29, 2013 1:22 PM
     
     
    Sign In to Vote
    0
    Perfect, thanks Chuck.