count totals ? - SQL Server Forums

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)

count totals ?

Author

Topic

Neutje
Starting Member

8 Posts

Posted - 2006-11-21 : 08:38:11

Hi all,

From this morning I'm trying to calculate the total amount with this query:

select (count(*) * table1.amount) as counter from table1 , table2
where table2.order=100 AND table1.order=100 group by table2.order,table1.amount

the result is only correct when there is only one product in the order
When there are more results I get more results from the query but I want to have the sum of the results of the query. I tried many ways but can't seem to find a way to get the correct result.

Hope anyone can help me

best regards,
Neut David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 08:43:15

Your query makes no sense at all. Start with this one

select		table1.order,
		sum(table2.amount) as counter,
		count(*) as somethingelse
from		table1
inner join	table2 on table2.order = table1.order
where		table1.order in (100, 101, 102)
group by	table1.order

And try your way what you REALLY want, since you can't explain it properly...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 08:51:27

It would be so much better if you could provide some proper sample data, the expected result of the provided sample data and maybe even some DDL.

Peter Larsson
Helsingborg, Sweden

Neutje
Starting Member

8 Posts

Posted - 2006-11-21 : 09:35:58

This query is a part of a bigger procedure I'm working on so It's very difficult to post data and so
In this query there are 2 different outputs (counter & somethingelse)
What i need is the sum of amount*count (sum(table2.amount * count(*))
When I know how to do that I can handle the rest of it

quote:
Originally posted by Peso

Your query makes no sense at all. Start with this one
select table1.order,
sum(table2.amount) as counter,
count(*) as somethingelse
from table1
inner join table2 on table2.order = table1.order
where table1.order in (100, 101, 102)
group by table1.order
And try your way what you REALLY want, since you can't explain it properly...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:43:36

The formula you describe fills no function.

You want the count of related records multiplied with some amount, take the sum of all that, and calculate the records (which is 1) and then multiply with amount again?

Peter Larsson
Helsingborg, Sweden

Neutje
Starting Member

8 Posts

Posted - 2006-11-21 : 09:52:18

I want the count of related records multiplied with the amount
Then I want the sum of all the matches

order 100 has 2 or more products with a differend amount
so i need to get the count of the same products multiplied with the amount
with that result i want to make the sum of all products from the order

order 100
5x product 1 with amount 22 = 110
1x product 2 with amount 27 = 27
the result I want = 137

the 5x is the count(*) I do
the amount can be different for each product
with my query I get the 2 results : 110 and 27
what I need to get is one result: 137
thats why i'm trying to make the sum of the results

hope i'm clear now

quote:
Originally posted by Peso

The formula you describe fills no function.

You want the count of related records multiplied with some amount, take the sum of all that, and calculate the records (which is 1) and then multiply with amount again?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:55:07

How does your tables look like? Post some DDL (definitions here).

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:57:31

Something like this?

SELECT		o.OrderNumber,
		SUM(ors.Items * ors.Price)
FROM		Orders o
INNER JOIN	OrderRows ors ON ors.[Order] = o.[Order]
GROUP BY	o.OrderNumber

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources