Total a query - 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

General SQL Server Forums

New to SQL Server Programming

Total a query

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 14:49:57

real simple question. how would i total this query?


select source,dof,count(dof) from xentel_chk group by source,dof order by source,dof

comes out like:

NM | 20070216 | 20
NM | 20070223 | 17

....

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 15:14:21

[code]
Select Sum(Dof),Sum(CntDof)
From
(
select source,dof,count(dof) as CntDoffrom xentel_chk group by
source,dof order by source,dof
) as f
[/code]

Chirag

http://chirikworld.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 15:21:32

after i fixed as CntDoffrom to CntDof From (type-o i assume)

i got the following error

Server: Msg 1033, Level 15, State 1, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 15:22:21

You don't need an ORDER BY in the derived table.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 15:22:59

Select source, Sum(CntDof)
From
(
select source,dof,count(dof) as CntDoffrom xentel_chk group by
source,dof
) as f
GROUP BY source
order by source

Peter Larsson
Helsingborg, Sweden

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 15:26:27

perhaps i was mis-understood, i'm looking for a result more like this:
NM | 20070216 | 20
NM | 20070223 | 17
total | null | 37

i only need to total the CntDof fields

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 15:30:41

select source, dof, count(dof) as CntDof
from xentel_chk
group by source,dof with rollup
order by source, dof

Peter Larsson
Helsingborg, Sweden

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 15:33:58

i got the same thing i got from the original query. on the messages tab, i see this:

(84 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

is that why?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 15:37:12

Select 'total' as Source, null AS dof, Sum(CntDof) as cntdof
From
(
select source, dof, count(dof) as CntDof
from xentel_chk
group by source, dof
) as f

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 15:40:12

Select * From
(
select source,dof,count(dof) CntDof from xentel_chk
group by source,dof
)
Order by CntDof
Compute Count(CntDof) By CntDof

Chirag

http://chirikworld.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 15:43:44

Peso, that just gives me the total, i'm looking for each count, with the total at the end.

Chirag
i get
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Order'.

when trying yours. never worked with "COMPUTE" so i'm not sure why....

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 15:47:51

Opps.. forgot to give alais to derived table..

Select * From
(
select source,dof,count(dof) CntDof from xentel_chk
group by source,dof
) as f
Order by CntDof
Compute Count(CntDof) By CntDof

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 15:50:02

Please provide some sample data and expected output, so we don't have to guess anymore...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 15:56:02

Here is a workable solution based on WITH ROLLUP as I suggested earlier.

-- prepare sample data
declare @sample table (source varchar(2), dof datetime)

insert	@sample
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070216' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323' union all
select	'NM', '20070323'

-- show the expected output
select		coalesce(source, 'total') AS source,
		dof,
		count(dof) as cntdof
from		@sample
group by	source,
		dof
with rollup
having		grouping(source) = grouping(dof)

Peter Larsson
Helsingborg, Sweden

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 15:58:31

sorry for being so vauge before. this is what i have/what i'm looking for.

current output:

NH 20060505 9
NH 20060512 3
NH 20060519 3
NH 20060526 1
NH 20060602 5
NM 20060505 101
NM 20060512 149
NM 20060519 106
NM 20060526 81
NM 20060602 17
Xen Xen 17929

what i'm looking for:

NH 20060505 9
NH 20060512 3
NH 20060519 3
NH 20060526 1
NH 20060602 5
NM 20060505 101
NM 20060512 149
NM 20060519 106
NM 20060526 81
NM 20060602 17
Total 18404

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 16:01:08

Peso, that worked PERFECT. thank you very much! now i'll have to figure out a way to work this into my procedure, thank you VERY much!

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-05-02 : 16:08:25

Peso, i'm fairly sure i understand most of this except:

having grouping(source) = grouping(dof)

could you give me an idea of the logic behind this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-02 : 16:34:35

Read Books Online about the grouping function.

Peter Larsson
Helsingborg, Sweden