compute sum with an alais for compute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

compute sum with an alais for compute

Author

Topic

cardgunner

326 Posts

Posted - 2007-05-23 : 11:51:24

Here is another one for the forum. I have a few things I would like to do. First is have an alais for the compute lines. I tried out Compute(...) as 'Anything' and it still comes out sum. Second is even though I converted the line it still is sum . You will see by the info i included. Third is getting all three compute lines on one line. Lastly to get a print to precede that line. Or at least above it. Take a look and I'll try to better explain if need be. I'm on my 2nd month of SQL and this forum has been a lifeline.

select	main.unit,
	main.type,
	main.allc,
	main.note,
	main.cost,
	main.book
from main
order by main.type, main.allc
compute sum(convert(decimal(9,2),main.note)), /* NOTE TOTAL */
	sum(convert(decimal(9,2),main.cost)), /* COST TOTAL */
	sum(convert(decimal(9,2),main.book)) /* BOOK TOTAL */
	by main.type, main.allc
compute sum(convert(decimal(9,2),main.note)), /* NOTE TOTAL */
	sum(convert(decimal(9,2),main.cost)), /* COST TOTAL */
	sum(convert(decimal(9,2),main.book)) /* BOOK TOTAL */
	by main.type

/*Sample Table*/
unit	type	allc	note	cost	book		
123	a	2	100	200	300
234	a	2	100	200	300	
345	a	1	100	200	300	
567	b	2	100	200	300	

/*Sample Results*/
unit	type	allc	note	cost	book		
345	a	1	100	200	300
			sum______
			100
				Sum¬¬¬¬¬¬______
				200	
					Sum________
					300
unit	type	allc	note	cost	book		
123	a	2	100	200	300
234	a	2	100	200	300	
			sum______
			200
				Sum______
				400	
					Sum________
					600
			sum______
			300
				Sum______
				600	
					Sum________
					900

unit	type	allc	note	cost	book		
567	b	2	100	200	300	
			sum______
			100
				Sum______
				200	
					Sum________
					300
/*desired results*/
unit	type	allc	note	cost	book		
345	a	1	100	200	300
			Nsum	Csum	Bsum_
Total by allc		100	200	300

unit	type	allc	note	cost	book		
123	a	2	100	200	300
234	a	2	100	200	300	
			Nsum	Csum	Bsum_
Total by allc		200	400	600

			Nsum	Csum	Bsum_
Total by type		300	600	900

Thank you for taking a look and hopefully a solution.

Card Gunner

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 08:48:07

Why don't you use ROLLUP operator instead? It seems more suitable for the situation.

-- prepare sample data
declare @t table
(
unit	int,
type	varchar(10),
allc	int,
note	int,
cost	int,
book int
)

insert @t
select 123,'a',2,100,200,300 union all
select 234,'a',2,100,200,300 union all
select 345,'a',1,100,200,300 union all
select 567,'b',2,100,200,300

-- desired output
select 
	allc, 
	coalesce(cast(type as varchar(50)), 'Total by Type'), 
	coalesce(cast(unit as varchar(50)), 'Total by Unit'), 
	note, cost, book from
(
select 
	allc, type, unit, sum(note) note, sum(cost) cost, sum(book) book
from @t
group by allc, type, unit with rollup
) t
where allc is not null and (type = 'a' or type is null)

Also, why don't you do the formatting in the front-end !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cardgunner

326 Posts

Posted - 2007-05-24 : 09:00:35

By the front-end you mean???

Card Gunner

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 09:13:52

By Front-end, I mean the application where you are going to show the data. Can be a crystal report, a datagrid control or even an html table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cardgunner

326 Posts

Posted - 2007-05-24 : 09:17:20

I'm using SQL Query Analyzer. I just found out yesterday how to right align numerics. Is this where I would go?

Card Gunner

cardgunner

326 Posts

Posted - 2007-05-24 : 09:21:08

Harsh, I'm not tryiong to be dumb but with the the below am I supposed to be sustituting actual field names? I have never declared anything. So I'm trying to understand by looking things up and asking questions. Like coelesce i looked up and found what that meant.
insert @t
select 123,'a',2,100,200,300 union all
select 234,'a',2,100,200,300 union all
select 345,'a',1,100,200,300 union all
select 567,'b',2,100,200,300

Card Gunner

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 09:26:52

quote:
Originally posted by cardgunner

I'm using SQL Query Analyzer. I just found out yesterday how to right align numerics. Is this where I would go?

Card Gunner

No. I mean where exactly is the output of this query is going to be used besides query analyzer? What's the purpose of writing this query? To generate a report?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 09:28:07

quote:
Originally posted by cardgunner

Harsh, I'm not tryiong to be dumb but with the the below am I supposed to be sustituting actual field names? I have never declared anything. So I'm trying to understand by looking things up and asking questions. Like coelesce i looked up and found what that meant.
insert @t
select 123,'a',2,100,200,300 union all
select 234,'a',2,100,200,300 union all
select 345,'a',1,100,200,300 union all
select 567,'b',2,100,200,300

Card Gunner

This part is for me to generate the test data so that I can verify whether results are comming as per your requirement. This part is not meant for you. You can just use final query and substitute @t with actual table name.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cardgunner

326 Posts

Posted - 2007-05-24 : 09:31:39

Ok, I took everything you gave me copied it into a new window. My results are



(4 row(s) affected)

allc                                    note        cost        book        
----------- ------------- ------------- ----------- ----------- ----------- 
          1 a             345                   100         200         300 
          1 a             Total by Unit         100         200         300 
          1 Total by Type Total by Unit         100         200         300 
          2 a             123                   100         200         300 
          2 a             234                   100         200         300 
          2 a             Total by Unit         200         400         600 
          2 Total by Type Total by Unit         300         600         900 

(7 row(s) affected)

I changed the varchar(50) to varchar(8) to it will fit.

Is this what I am supposed to get?

Card Gunner

cardgunner

326 Posts

Posted - 2007-05-24 : 09:37:18

I'm starting to get this. Again excuse my newness. The report will be copied into a .txt file. The reort is used by my managers to see what the numbers are. What they are on the hook for.

So they look at the report, look at the totals, then they look at the underlying info that makes up those totals. There they make up conclusions as we have too many 123's. or convert 234 or things like that.

Card Gunner

cardgunner

326 Posts

Posted - 2007-05-24 : 09:43:08

So this report is 65 pages long. Where as alot of that is blank space created by the compute as indicated in first post. If you can imagine those sum line apearing for 20 different types where each type can have both 1 or 2 for allc. And then they cannot look at the sums and know what they are without looking at the inforamtion above them. I'm getting longwinded. sorry.

Card Gunner

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-24 : 10:13:42

You should not be trying to write reports using Query Analyzer! use a proper reporting tool -- Access, Crystal, SQL Reporting Services, Excel, etc ... SQL is just a data querying and manipulating language, and Query Analyzer is just a query analysis tool -- they are not designed for presenting data to users and formatting things to make them look nice. never, never, never attempt to format output in SQL ... it's just trying to use a hammer as a screw driver . Use the right tool for the job.

- Jeff
http://weblogs.sqlteam.com/JeffS

cardgunner

326 Posts

Posted - 2007-05-24 : 10:30:22

Thanks Jeff. I will have to do some research on this. I know Access real well but is it the best tool. ??? I've heard Crystal alot but never seen it or used.

Card Gunner

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-24 : 10:36:47

it all depends on how you want to deploy it. Access is great at writing reports, but you need Access installed on your PC to execute them. I use it for one-off things or reports that I will run myself and "PDF" to other people. If you want a report that people can run off of an Intranet or something like that, then Reporting Services is the way to go, and Crystal is also an option. It all depends on how you want to deliver it the content and how you want users to access the reports.

- Jeff
http://weblogs.sqlteam.com/JeffS

cardgunner

326 Posts

Posted - 2007-05-24 : 10:57:32

as far as the soltion using roll up... unfortunely Total unit was used and not total type. I tried replacing the field names in the code but cannot get the desried table below

/*Desired Table*/
unit	type	allc	note	cost	book		
123	a	2	100	200	300
234	a	2	100	200	300	
null	null	ttlallc	300	600	900
345	a	1	100	200	300
null	null	Ttlallc	100	200	300	
null	Ttltype	null	400	800	1200
567	b	2	100	200	300
null	null	ttlallc	100	200	300
null	Ttltype	null	100	200	300

or better yet this. This is almost perfect.


/*Desired Table*/
unit	type	allc	note	cost	book		
123	a	2	100	200	300
234	a	2	100	200	300	
		ttlallc	300	600	900

345	a	1	100	200	300
		Ttlallc	100	200	300	

	Ttltype		400	800	1200

unit	type	allc	note	cost	book
567	b	2	100	200	300
		ttlallc	100	200	300
	
	Ttltype		100	200	300

But if I used Access, I'm pretty sure I could format the report so the information would show correctly. I used to do all of this, somewhat in Access. But I built too big of a sandbox. I'm not sure how I would import the information first.

Can I link the table? I have only been able to link other Access tables and Excel Sheets.

Card Gunner

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-24 : 11:15:53

If you use Access or any other reporting tool that supports grouping and subtotalling, you don't need to do anything -- you already have the data you need with a simple SELECT.

You can create an ADP project in MS Access that links directly to SQL Server, and you get all of the reporting features of Access linked directly to your SQL database. Select File->New->Project using Existing Data to create and link an ADP project to your SQL database. Now you can just write simple, valid SQL statements and let your reporting tool do your subtotalling and group headers and footers and running totals and paging and all that -- the way it is meant to be!

- Jeff
http://weblogs.sqlteam.com/JeffS

cardgunner

326 Posts

Posted - 2007-05-24 : 11:50:28

Sounds good to me thanks Jeff.

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources