Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Have Total Show Under User

    (SQL Server 2008) I want to display a total for each user that is returned from my query result. I was trying the union all route as that is what I am most comfortable with, however, the result set returns the total row at the bottom instead of under each user. This is the desired result set
    Click image for larger version. 

Name:	Capture.PNG 
Views:	5 
Size:	11.4 KB 
ID:	16559

    Here is my query, what do I need to alter to have the output display like so? (I want to avoid using a external "lookup" table for the order as this will grow exponentially over time)
    Code:
    Create Table #Test
    (
    	id varchar(50),
    	name varchar(500),
    	out1 int,
    	out2 int,
    	total4day int,
    	[date] date
    )
    
    
    Insert Into #Test Values
    ('16','Roy D Mercer', 32, 8, 480, '06/01/2015'),
    ('16','Roy D Mercer', 12, 61, 480, '06/02/2015'),
    ('16','Roy D Mercer', 18, 30, 480, '06/03/2015'),
    ('16','Roy D Mercer', 6, 40, 480, '06/04/2015'),
    ('16','Roy D Mercer', 5, 10, 480, '06/05/2015'),
    ('11','Leonard Moped', 23, 11, 480, '06/01/2015'),
    ('11','Leonard Moped', 10, 14, 480, '06/02/2015'),
    ('11','Leonard Moped', 5, 5, 480, '06/03/2015'),
    ('11','Leonard Moped', 20, 10, 480, '06/04/2015'),
    ('11','Leonard Moped', 80, 11, 480, '06/05/2015')
    
    Select 
    name,
    out1,
    out2
    FROM #Test
    UNION ALL
    select 
    'Total For: ' + name,
    SUM(out1),
    SUM(out2)
    from #Test
    GROUP BY name
    ORDER BY name ASC
    
    drop table #Test

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The best answer is to handle it in your reporting package or client application.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    The query results are returned to a C# dataset then presented in a datagrid. Is that method capable of handling the method I am asking?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jo15765 View Post
    The query results are returned to a C# dataset then presented in a datagrid. Is that method capable of handling the method I am asking?
    Yes, a .Net DataGrid can have both headers and footers.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    Yes, a .Net DataGrid can have both headers and footers.

    -PatP
    I have used footers before, but never in an instance of seperating between each different value being returned in a field.

  6. #6
    Join Date
    Sep 2010
    Posts
    15
    You can add a column to sort in your 2 selects, then sort by that column. Here is example

    Code:
    Create Table #Test
    (
    	id varchar(50),
    	name varchar(500),
    	out1 int,
    	out2 int,
    	total4day int,
    	[date] date
    )
    
    
    Insert Into #Test Values
    ('16','Roy D Mercer', 32, 8, 480, '06/01/2015'),
    ('16','Roy D Mercer', 12, 61, 480, '06/02/2015'),
    ('16','Roy D Mercer', 18, 30, 480, '06/03/2015'),
    ('16','Roy D Mercer', 6, 40, 480, '06/04/2015'),
    ('16','Roy D Mercer', 5, 10, 480, '06/05/2015'),
    ('11','Leonard Moped', 23, 11, 480, '06/01/2015'),
    ('11','Leonard Moped', 10, 14, 480, '06/02/2015'),
    ('11','Leonard Moped', 5, 5, 480, '06/03/2015'),
    ('11','Leonard Moped', 20, 10, 480, '06/04/2015'),
    ('11','Leonard Moped', 80, 11, 480, '06/05/2015')
    
    ;WITH cte AS(
    Select 
    name,
    out1,
    out2, toSort=name
    FROM #Test
    UNION ALL
    select 
    'Total For: ' + name,
    SUM(out1),
    SUM(out2), toSort=name+'z_'
    from #Test
    GROUP BY name
    )
    SELECT name,out1,out2 FROM cte ORDER BY toSort ASC
    
    drop table #Test
    Another solution is using Cube and combine with grouping to sort as expected

Posting Permissions

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