Summarizing Several Fields of a table.

Question

Summarizing Several Fields of a table.

Joseph Henry

SSChasing Mays

Points: 626
More actions
August 31, 2006 at 12:56 pm

#114774

Hello All:
I'm trying to create a report that summarizes/counts several field in a table. Right now I am using a stored procedure with several Count statements. I'm also making manual changes to the procedure to accomodate different requests. I'm presenting the report to users as a simple text file.
What I would like to do is allow simple parameters to the stored procedure for Plan, Product etc. And I would also like to present the report in a more presentable manner. I'm using an access .adp as the front end so that poses the usual problems.
One place I getting stuck is when I use the variables I declared. For example, when I have a product variable... I try to concatenate it into a string for the table name. In the example below, SWL would be the product. So I would have a variable for @product and a variable for @table. The concatenation would be @table = 'tbl' + @product + '_CASTLmo' This is not working when I use @table in the from statement. I'm not sure if this can work or not. Do I need to make an entire quoted sql statement and then use sp_executesql?
Also if I am going in the wrong direction and anyone has any suggestions for different ways to handle this please let me know.
--Age Count
SELECT bytAgepi1 AS Age,
COUNT(bytAgepi1) AS 'Age Count'
from tblSWL_CASTLmo
where txtTestPlan = 'Plan1'
GROUP BY bytAgepi1
ORDER BY 'Age Count' DESC
--Class Count
SELECT txtClasspi1 AS Class,
COUNT(txtClasspi1) AS 'Class Count'
from tblSWL_CASTLmo
where txtTestPlan = 'Plan1'
GROUP BY txtClasspi1

As always any help is greatly appreciated.
Thanks,
Joe

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Have u tried using Cube Operator. This will help you summarize at different levels and you can query the columns and summary that you need.

SELECT Item, Color, SUM(Qty) AS QtySum

FROM tbl_Inventory

GROUP BY Item, Color WITH CUBE

will return

Chair Blue 101

Chair Red 210

Chair NULL 311

Table Blue 124

Table Red 223

Table NULL 347

NULL NULL 658

NULL Blue 225

NULL Red 433

Hope this helps.

Thanks

Sreejith

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 2

Hi

I think for your selects to work using a varaible as a table name, then you will have to create the select as a string and exec that string.

In terms of your output. I build a lot of stored procedures for use with Crystal Reports, so I use the stored procedure to stage my data and to provide reasonably formatted data to the report. The reporting exercise is almost as simple as adding the right controls in the right postion.

If you are wanting to use Access reports, you can exec the stored procedure in a pass-through query and use the resulting data set as the source for you report. If you are reasonably proficient with VBA, you could eliminate the pass-through query by creating a connection to SQL Server and using a querydef object to produce a recordset. This method is arguably the best, but less transparent ie a pass-through query enables you to see the data before it hits the report - useful for debugging.

If your parameters for the sp a going to be different each time, then you could build a form to handle the criteria selection and to modify the text of the pass-through as a result.

I hope this provides you with some insights and some ideas of how you may progress.

Cheers

Rowan

Sergiy SSC Guru Points: 110192 More actions · Answer 3

If you're going to make all calculations by yourself then keep information about different products in different tables, as it's suitable for you, human being.

If you wanna make SQL Server do the calculations for you then store all products in single table as it's suitable for SQL Server, RDMS.

So, choose your way.

_____________
Code for TallyGenerator

RoelofK68 SSC-Addicted Points: 436 More actions · Answer 4

You can also a query Sreejith's statement, using a case statyement, that should properly pivot the data, if that is what you need.

It is probably not the best way of doing it, but it works.

select p.Item, sum(case Color when 'RED' then p.QtySum end) as RedSum,
sum(case Color when 'BLUE' then p.QtySum end) as BlueSum,
Sum(case Color when null then p.QrySum end) as OtherSum
from (SELECT Item, Color, SUM(Qty) AS QtySum
FROM tbl_Inventory
GROUP BY Item, Color WITH CUBE)p
Group by p.Item

<hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,

Roelof
<a href='http://' class='authorlink' target='_blank'></a>

--There are only 10 types of people in the world. Those who understand binary, and those who don't.