making a sum on count - 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

SQL Server Development (2000)

making a sum on count

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-26 : 07:23:25

can i make somthing klike

selct SUM(Count(id))

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-26 : 09:54:10

Eh? What for? "select SUM(Count(id))" give the same answer as "select Count(id)". Even for multiple groups. Now, AVG(Count(id) might give an answer that is different, though though of questionable statistical value. Regardless, I think you would need to perform the first aggregate function in a subquery, as I doubt you can't double up aggregations on a single query, and you would want separately defined "GROUP BY" clauses anyway.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-26 : 09:56:07

[code]select sum(cnt)
from
(
select count(*) as cnt from sometable
) as a[/code]

----------------------------------
'KH'
It is inevitable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-27 : 06:35:32

quote:
Originally posted by khtan

select sum(cnt)
from
(
select count(*) as cnt from sometable
) as a

----------------------------------
'KH'
It is inevitable

Because there is no group by clause that group it by other column, your method will give you the same result of count(col)


Select sum(data) as data from
(
	Select count(data) as data from
	(
		Select 12 as data union all select 10
	) T
) T

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-06 : 03:17:06

my count had a group by on the id
and after i group by group's i want to summ all the rsults

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-06 : 03:30:34

[quote] my count had a group by on the id
and after i group by group's i want to summ all the rsults[quote]
Actually that is a good as removing the group by away and just count(*) as Madhivanan has pointed out.

See here

declare	@table table
	(
		id	int,
		value	int
	)

insert into @table
select	1, 10	union all
select	1, 11	union all
select	2, 20	union all
select	2, 21	union all
select	3, 30	union all
select	4, 40

select	id, count(*)
from	@table
group by id
-- result :
--	1, 2
--	2, 2
--	3, 1
--	4, 1

select 	sum(cnt)
from
(
	select	id, count(*) as cnt
	from	@table
	group by id
) as c
-- result = 6

select	count(*)
from	@table
-- result = 6

----------------------------------
'KH'

zolee
Starting Member

6 Posts

Posted - 2007-11-03 : 19:23:55

Hello,

I have basically the same problem and I thought that sum of count would be the solution. But because there is no such thing, please give me some advice.

I have 2 tables, one for products and another for orders.
I want to list sum of orders per product and per day.

The data I have stored is something like this:
prid, date, value
1, 2007-01-01, 5
1, 2007-01-01, 5
1, 2007-01-02, 4
1, 2007-01-02, 1
2, 2007-01-01, 1
3, 2007-01-01, 9
3, 2007-01-01, 1

The desired output is, sum per product grouped daily:

prid, date, value
1, 2007-01-01, 10
1, 2007-01-02, 5
2, 2007-01-01, 1
3, 2007-01-01, 10

I'm sure it's not very complicated but just can't figure out..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-03 : 19:44:35

With that sample:

Select Prid,[Date],Sum(Value) as Total
From [Table]
Group by Prid,Date
Order by Prid,Date

zolee
Starting Member

6 Posts

Posted - 2007-11-04 : 04:20:56

Thank you.
In fact this query didn't work as it was, but I figured out why!
The [date] field is a datetime, containing time of insert, too. This messed up the group by, so I added a convert(varchar, [date], 2) and it works fine!

Now the next thing: I'd like to list the totals only for the last N days. I mean workdays so I decided not to use something like DATEADD(day, -7, GETDATE()) for filtering.
Instead I came up with a subquery:

select <fields>
from table
where (convert(varchar, [Date], 2) in
(select distinct top (7) convert(varchar, [Date], 2) AS d
from table
order by d desc))
...
group by ...

Question: it seems to work but is there a simpler or more efficient way?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-04 : 09:05:50

[code]SELECT prid,
date = DATEADD(DAY, DATEDIFF(DAY, 0, date), 0),
value = SUM(value)
FROM yourtable
GROUP BY prid, DATEADD(DAY, DATEDIFF(DAY, 0, date), 0)[/code]

KH
[spoiler]Time is always against us[/spoiler]

zolee
Starting Member

6 Posts

Posted - 2007-11-04 : 13:08:48

Thanks!

zolee
Starting Member

6 Posts

Posted - 2007-11-05 : 04:30:16

Please let someone flow a little more knowledge:

with the given table, how can I list the fist (or the last) order for a given [prid], for every day, separately?
[Date] is datetime so holds time of order too.

So I'd have to select the smallest (or the largest) [Date] value for every disinct day. Which means first step is to create a list of distinct days as a subquery? then query for .. umm, I'm a little bit mixed up already

Desired output would be with dummy data:

prid, first, firstval, last, lastval
1, 2007-01-01 09:00:00, 1, 2007-01-01 14:55:00, 3
1, 2007-01-02 09:05:10, 2, 2007-01-02 14:58:13, 1

(note DATE is the same in every row)

thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 07:22:37

try this

SELECT 	*
FROM   	(
	       SELECT prid,
	              date     = DATEADD(DAY, DATEDIFF(DAY, 0, date), 0),
	              min_date = MIN(date),
	              max_date = MAX(date)
	       FROM   yourtable
	       GROUP BY prid, DATEADD(DAY, DATEDIFF(DAY, 0, date), 0)
       	) m
       	INNER JOIN yourtable t1
       	ON	m.prid		= t1.prid
       	AND	m.min_date	= t1.date
	INNER JOIN yourtable t2
	ON	m.prid		= t2.prid
	AND	m.max_date	= t2.date

KH
[spoiler]Time is always against us[/spoiler]

zolee
Starting Member

6 Posts

Posted - 2007-11-05 : 10:32:58

Thank you very much. I think this
DATEADD(DAY, DATEDIFF(DAY, 0, date), 0)
thing is so useful, I will create a function for it. Wonder why isn't this already there? Something like DATEPART()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 10:40:58

read this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

KH
[spoiler]Time is always against us[/spoiler]

zolee
Starting Member

6 Posts

Posted - 2007-11-05 : 15:04:05

May your fountain never dry out and continue to flow the Yak knowledge upon beginners like me for ever and ever. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources