SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 making a sum on count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 02/26/2006 :  07:23:25  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 02/26/2006 :  09:54:10  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 02/26/2006 :  09:56:07  Show Profile  Reply with Quote
select sum(cnt)
from
(
	select count(*) as cnt from sometable
) as a


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

It is inevitable
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/27/2006 :  06:35:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 03/06/2006 :  03:17:06  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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 -:)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 03/06/2006 :  03:30:34  Show Profile  Reply with Quote
[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'


Go to Top of Page

zolee
Starting Member

6 Posts

Posted - 11/03/2007 :  19:23:55  Show Profile  Reply with Quote
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..
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/03/2007 :  19:44:35  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
With that sample:

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

Go to Top of Page

zolee
Starting Member

6 Posts

Posted - 11/04/2007 :  04:20:56  Show Profile  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/04/2007 :  09:05:50  Show Profile  Reply with Quote
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)



KH
Time is always against us

Go to Top of Page

zolee
Starting Member

6 Posts

Posted - 11/04/2007 :  13:08:48  Show Profile  Reply with Quote
Thanks!
Go to Top of Page

zolee
Starting Member

6 Posts

Posted - 11/05/2007 :  04:30:16  Show Profile  Reply with Quote
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!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/05/2007 :  07:22:37  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

zolee
Starting Member

6 Posts

Posted - 11/05/2007 :  10:32:58  Show Profile  Reply with Quote
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()
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/05/2007 :  10:40:58  Show Profile  Reply with Quote
read this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762


KH
Time is always against us

Go to Top of Page

zolee
Starting Member

6 Posts

Posted - 11/05/2007 :  15:04:05  Show Profile  Reply with Quote
May your fountain never dry out and continue to flow the Yak knowledge upon beginners like me for ever and ever. Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000