Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: Must be a better way of producing a summary table

    Hi,

    I want to produce a summary table of amounts per status per user.

    I have 2 tables:

    Invoices:
    Code:
    user_id, amount, status
    1,       10,    S
    2,       20,    P
    3,       30,    P
    3,       40,    E
    and

    Users:
    Code:
    user_id, name
    1,       user A
    2,       user B
    3,       user C
    and I want to produce a summary table like this:

    Code:
           S   P   E   Total
    user A 10         10
    user B     20     20
    user C     30 40 70
    What I have is:


    Code:
    SELECT Users.name, 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')), 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')), 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')), 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E'))) 
    FROM Invoices 
    LEFT JOIN Users ON Users.user_id = Invoices .user_id 
    GROUP BY Invoices.user_id, Users.name 
    ORDER BY Users.name
    This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory. There must be a neater way, I'd appreciate any ideas.

    Many thanks in advance
    Rachel

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I just did this for someone else...maybe this will help

    Code:
    CREATE TABLE #myTable99([ID] int, [type] varchar(20), [qty] int)
    GO
    
    INSERT INTO #myTable99([ID], [type], [qty])
    SELECT 1,	'Purchase',	10 UNION ALL
    SELECT 2,	'Purchase',	20 UNION ALL
    SELECT 3,	'Sale',		 8 UNION ALL
    SELECT 4,	'Purchase',	15 UNION ALL
    SELECT 5,	'Sale',		 2 UNION ALL
    SELECT 6,	'Sale',		 8
    GO
    
    SELECT * FROM #myTable99
    GO
    	SELECT t.[ID], t.[qty], t.[type], xxx.[SUM_qty] 
          FROM (
    				SELECT l.[ID]
    					 , SUM(r.[qty]) AS [SUM_qty]
    				  FROM #myTable99 l
    			INNER JOIN #myTable99 r
    					ON r.[ID] <= l.[ID]
    			  GROUP BY l.[ID]) AS XXX
    INNER JOIN #myTable99 t
    		ON xxx.[ID] = t.[ID]
      ORDER BY t.[id]
    GO
    DROP TABLE #myTable99
    GO
    And Sunita posted another

    SQL Server Forums - Aggregate Sum
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2009
    Posts
    21
    Thanks, I'll give it a go.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you are trying to create is called a "pivot table" and you can use a client application like Excel to do these quickly, easily, and dynamically.

    It is generally a bad idea to do pivot tables within a database unless you have a very specific and rather small output table in mind. If the output has more than a dozen columns or a thousand rows, the odds are good that you want to use another tool than a SQL script to create it. SQL Server Analysis Services is designed to handle this kind of problem.

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

  5. #5
    Join Date
    Sep 2009
    Posts
    21
    Hi Pat,

    Thanks for the comment, useful for me to learn the jargon for what I'm trying to do! Unfortunately I don't have that kind of option, the tables and application already exist - I was just tasked with coming up with a line of SQL to create the required report.

  6. #6
    Join Date
    Sep 2009
    Posts
    21
    Hi Brett,

    Was trying to work with your suggestion. I can't use an interim table or view even for that matter as I need all the original table fields to be available in the final SQL query because users of this report add their own WHERE clauses, eg where team = .. or where customer = ... or where category = ... etc.

    I did however make a slight improvement of my own suggestion

    Code:
    SELECT Users.name, 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')), 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')), 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')), 
    (SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E'))) 
    FROM (SELECT * FROM Invoices WHERE status IN ('S','P','E'))
    LEFT JOIN Users ON Users.user_id = Invoices .user_id 
    GROUP BY Invoices.user_id, Users.name 
    ORDER BY Users.name
    This then removes vast numbers of records from the sum part of the query, as most invoices are closed and I am not hitting the system memory limit anymore.

    Thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Users.name
         , SUM(CASE WHEN Invoices.status = 'S'
                    THEN amount ELSE NULL END) AS 'S'
         , SUM(CASE WHEN Invoices.status = 'P'
                    THEN amount ELSE NULL END) AS 'P'
         , SUM(CASE WHEN Invoices.status = 'E'
                    THEN amount ELSE NULL END) AS 'E'
         , SUM(amount) AS Total
      FROM Invoices 
    INNER
      JOIN Users 
        ON Users.user_id = Invoices.user_id 
     WHERE Invoices.status IN ('S','P','E')
    GROUP 
        BY Users.name 
    ORDER 
        BY Users.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2009
    Posts
    21
    Wonderfully simple - many thanks r937

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How is that a running total?

    In my Query ALL of the columns ARE available because I join back to your original table!

    Just do SELECT * THEN for goodness sake

    And if you want a grouping then just add a GROUP BY for goodness sake...those queries are LIMITED to particular values...what if they have more than those values?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2009
    Posts
    21
    Sorry Brett, I'm not a fluent SQL user, so I can only do what I know what I can do, if that makes sense. I didn't want a running total, but a total of each row. I've extended r937s version to fit my needs. I did try your suggestion but I couldn't get it to be able to use fields in the original table, not sure what I did wrong.

Posting Permissions

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