Add a calculated field without grouping

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

Transact-SQL (2000)

Add a calculated field without grouping

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 08:33:05

I have a table and I want to transform it in a view but with an extra column that makes some calculations.
The table has 107393 rows and the view I managed to create by selecting some columns from the original table and a calculation field has 99242.
I believe that the grouping 'hides' some rows.
I want to find a way to have all my original table rows and the extra calculation field.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 08:46:26

Remove grouping or post your complete view definition here to get some fruitful advice.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 08:53:33

SELECT ((SELECT SUM(QTY) AS test FROM posuser.LABSTORE WHERE ITEM_CODE = 873) /COUNT(DISTINCT RECEIPT)) AS column1, REC_TIME , FO_DAY , RECEIPT , QTY, ITEM_CODE
FROM posuser.LABSTORE
GROUP BY REC_TIME , QTY , FO_DAY , RECEIPT, ITEM_CODE

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 08:58:16

If you want count of RECEIPT, then why you are including RECEIPT column in GROUP BY clause?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-04 : 09:04:01

Impossible to know what you are trying to do without details, but my guess is you just need something like this:

SELECT ITEM_CODE, REC_TIME , FO_DAY, SUM(QTY) / COUNT(DISTINCT RECEIPT) AS column1
FROM posuser.LABSTORE
WHERE ITEM_CODE = 873
GROUP BY ITEM_CODE, REC_TIME , FO_DAY

Be sure that you understand how GROUP BY and aggregate functions work. more on that here:

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server
http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables

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

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 09:04:07

My boss want to get in a report the number in the parenthses.The report(crystal report) has already a crosstab that calculates another thing (posuser.LABSTORE.TOTAL) and it takes its results from a view that it is a simple copy of some columns of the original table so the view has 107393 rows.I want to find a way to add an extra column that will give me this number so that I can add it to the report.
Any ideas are welcome.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-03-04 : 09:05:26

jsmith it is not what I want.If you see abobe the where conditional is in the parentheses and it is there for a reason.thnx anyway for helping....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-04 : 09:53:14

Well, if you ever feel like explaining precisely what you want with an example I am sure we can help you. You keep giving general information that doesn't help us to help you. Guessing what you really need is fun and all that, but isn't it a little better and a little quicker for all involved if you explain it clearly?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources