Must be a better way of producing a summary table

Rachel_B · #1 (**permalink**) 01-05-12, 13:30

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

Brett Kaiser · #2 (**permalink**) 01-05-12, 14:10

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

Rachel_B · #3 (**permalink**) 01-05-12, 14:26

Thanks, I'll give it a go.

Pat Phelan · #4 (**permalink**) 01-05-12, 15:15

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

Rachel_B · #5 (**permalink**) 01-06-12, 05:50

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.

Rachel_B · #6 (**permalink**) 01-06-12, 08:38

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

r937 · #7 (**permalink**) 01-06-12, 09:58

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

Rachel_B · #8 (**permalink**) 01-06-12, 10:37

Wonderfully simple - many thanks r937

Brett Kaiser · #9 (**permalink**) 01-09-12, 16:28

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?

Rachel_B · #10 (**permalink**) 01-10-12, 06:06

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech