How can I sum the number of attributes from one table and export the count to a temp table as columns with 1 row per unique key?

Question

How can I sum the number of attributes from one table and export the count to a temp table as columns with 1 row per unique key?

markie76

SSC Rookie

Points: 28
More actions
September 7, 2011 at 8:57 am

#247432

Hi Guys,
I have a table of data with invoice line data in it (each invoice line = 1 row so invoice number is duplicated by however many lines on the invoice).
I need to create a temporary table whose unique key is the invoice number (no duplicates - 1 row per invoice_no) and it is to sum ( COUNT(tax_code) ) the tax_codes used on each invoice and write the sum to a separate column in the temp table. So the sum of the 4 columns in the temp table per invoice = the number of rows in the invoicelines table per inv_no.
Example;
I have tired and failed with;
CREATE & INSERT - It just inserts new rows.
SELECT INTO - I can't figure out if its possible to incorporate the 4 separate COUNT(tax_code) where tax_code = blank,1,2 or 3 selection criteria into a single statement
UPDATE - Can't figure out how to get it to test if the inv_no already exists and update the relevant column, if not create a new row.
Any help much appreciated.
Thanks,
Mark.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Mark, i know of a easy way to do that .

First, your visual representation of the data is good. But it would greatly help if you had provided the data as INSERT INTO table scripts, this way we need not type each row and find an answer. I see you are pretty new to the site, so no worries.. please provide us your table as "CREATE TABLE " , sample data as "INSERT INTO " statements.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Here is how you must present data so that people viewing the thread will hop on to it asap

Sample data:

IF OBJECT_ID('TempDB..#TempData') IS NOT NULL

DROP TABLE #TempData

CREATE TABLE #TempData

(

INV_NO INT ,

LINE_NO INT ,

TAX_CODE INT

)

CREATE NONCLUSTERED INDEX IX_#TempData_AllColumns

ON #TempData ( INV_NO )

INCLUDE ( TAX_CODE ,LINE_NO )

INSERT INTO #TempData ( INV_NO ,LINE_NO ,TAX_CODE )

SELECT 1001, 1 , NULL

UNION ALL SELECT 1002, 1 , 1

UNION ALL SELECT 1003, 1 , 3

UNION ALL SELECT 1004, 1 , 3

UNION ALL SELECT 1005, 1 , 3

UNION ALL SELECT 1001, 2 , 1

UNION ALL SELECT 1002, 2 , 1

UNION ALL SELECT 1003, 2 , 2

UNION ALL SELECT 1004, 2 , 2

UNION ALL SELECT 1005, 2 , 2

UNION ALL SELECT 1001, 3 , NULL

UNION ALL SELECT 1002, 3 , 1

UNION ALL SELECT 1003, 3 , 2

UNION ALL SELECT 1004, 3 , NULL

UNION ALL SELECT 1005, 3 , NULL

UNION ALL SELECT 1001, 4 , 2

UNION ALL SELECT 1004, 4 , NULL

UNION ALL SELECT 1003, 6 , NULL

UNION ALL SELECT 1004, 5 , NULL

UNION ALL SELECT 1004, 5 , 3

Code to produce your expected result :

SELECT INV_NO

,BLANK = SUM ( CASE WHEN TAX_CODE IS NULL THEN 1 ELSE 0 END )

,TAX1 = SUM ( CASE WHEN TAX_CODE = 1 THEN 1 ELSE 0 END )

,TAX2 = SUM ( CASE WHEN TAX_CODE = 2 THEN 1 ELSE 0 END )

,TAX3 = SUM ( CASE WHEN TAX_CODE = 3 THEN 1 ELSE 0 END )

FROM #TempData

GROUP BY INV_NO

If you want to insert the results into a temp table, then

IF OBJECT_ID('TempDB..#TempResults') IS NOT NULL

DROP TABLE #TempResults

CREATE TABLE #TempResults

(

INV_NO INT ,

BLANK INT ,

TAX1 INT,

TAX2 INT,

TAX3 INT

)

INSERT INTO #TempResults

SELECT INV_NO

,BLANK = SUM ( CASE WHEN TAX_CODE IS NULL THEN 1 ELSE 0 END )

,TAX1 = SUM ( CASE WHEN TAX_CODE = 1 THEN 1 ELSE 0 END )

,TAX2 = SUM ( CASE WHEN TAX_CODE = 2 THEN 1 ELSE 0 END )

,TAX3 = SUM ( CASE WHEN TAX_CODE = 3 THEN 1 ELSE 0 END )

FROM #TempData

GROUP BY INV_NO

Hope this helps. In future, if you post, please follow how i posted sample data 🙂 the way you posted your expected result is too good, keep it up..

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

You can use SELECT INTO also , to insert the result set into a table

SELECT INV_NO

,BLANK = SUM ( CASE WHEN TAX_CODE IS NULL THEN 1 ELSE 0 END )

,TAX1 = SUM ( CASE WHEN TAX_CODE = 1 THEN 1 ELSE 0 END )

,TAX2 = SUM ( CASE WHEN TAX_CODE = 2 THEN 1 ELSE 0 END )

,TAX3 = SUM ( CASE WHEN TAX_CODE = 3 THEN 1 ELSE 0 END )

INTO #TempResults -- Your temp results table name

FROM #TempData

GROUP BY INV_NO

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Well done, CC.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

@mark-3,

See the following article for how the method Cold Coffee used works.

http://www.sqlservercentral.com/articles/T-SQL/63681/

To see how to post data in the future to help us help you better, please see the first link in my signature below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

ColdCoffee

SSC-Dedicated

Points: 39972

September 8, 2011 at 1:53 am

#1378389

Jeff Moden (9/7/2011)
Well done, CC.

Thanks, Jeff..

markie76 SSC Rookie Points: 28 More actions · Answer 7

ColdCoffee (9/7/2011)
You can use SELECT INTO also , to insert the result set into a table
SELECT INV_NO
,BLANK = SUM ( CASE WHEN TAX_CODE IS NULL THEN 1 ELSE 0 END )
,TAX1 = SUM ( CASE WHEN TAX_CODE = 1 THEN 1 ELSE 0 END )
,TAX2 = SUM ( CASE WHEN TAX_CODE = 2 THEN 1 ELSE 0 END )
,TAX3 = SUM ( CASE WHEN TAX_CODE = 3 THEN 1 ELSE 0 END )
INTO #TempResults -- Your temp results table name
FROM #TempData
GROUP BY INV_NO

Thanks for your detailled response - it works beautifully! :w00t:

Appologies for not posting my query examples - in my stressed state I omitted to check for house rules but know for next time.

Jeff Moden (9/7/2011)
@Mark,
See the following article for how the method Cold Coffee used works.
http://www.sqlservercentral.com/articles/T-SQL/63681/
To see how to post data in the future to help us help you better, please see the first link in my signature below.

Thanks for the link - looks interesting and 50% of it isn't even mentioned in my beginners sql book :doze:

I've read the links in your sig and am sorry I'm an etiquette breaker. I did initially paste the tables into the post window but couldn't find a way for it to retain the format (spaces in between) so then screenshot popped into my head :blush:

Thanks again.