Count Function Question!

8 Jul 2005 11:16 AM

marcmc

Please use the working example here.
In the 1st sql statement i.) I get 3 rows.
How can I get the sql statement in ii.) to return one row with 3 in it (as
the count)?

create table marcClmCount(
m varchar(2),
m1 float,
m2 float)

insert into marcClmCount values('M1', 5.00, 10.00)
insert into marcClmCount values('M2', 5.00, 11.00)
insert into marcClmCount values('M3', 5.00, 12.00)
insert into marcClmCount values('M4', 0.00, 0.00)

-- i.)
SELECT    m
FROM    marcClmCount
GROUP BY m
HAVING    (ROUND(sum((m1 * m2)), 2.0)) <> 0.00

-- ii.)
SELECT    count(m)
FROM    marcClmCount
GROUP BY m
HAVING    (ROUND(sum((m1 * m2)), 2.0)) <> 0.00

-- drop table marcClmCount

8 Jul 2005 11:25 AM

Razvan Socol

Try this:

SELECT COUNT(*) FROM (
SELECT   m
FROM     marcClmCount
GROUP BY m
HAVING   (ROUND(sum((m1 * m2)), 2.0)) <> 0.00
) X

This may also work (but you may get different results):

SELECT COUNT(DISTINCT m)
FROM marcClmCount
WHERE m1<>0 and m2<>0

Razvan

8 Jul 2005 11:34 AM

marcmc

Thankyou Razvan
What does the X do (a cross reference), can I look it up online in in Books
On Line?

8 Jul 2005 11:50 AM

Tibor Karaszi

It is naming the inner result, so it can be used as a table (with the name X) in the outer query.
Read about "Derived Tables" in Books Online.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"marcmc" <mar***@discussions.microsoft.com> wrote in message
news:EA65840E-546C-486E-9D5D-849099B79913@microsoft.com...
> Thankyou Razvan
> What does the X do (a cross reference), can I look it up online in in Books
> On Line?

8 Jul 2005 1:11 PM

marcmc

perfect

Show quote

"Tibor Karaszi" wrote:

> It is naming the inner result, so it can be used as a table (with the name X) in the outer query.
> Read about "Derived Tables" in Books Online.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "marcmc" <mar***@discussions.microsoft.com> wrote in message
> news:EA65840E-546C-486E-9D5D-849099B79913@microsoft.com...
> > Thankyou Razvan
> > What does the X do (a cross reference), can I look it up online in in Books
> > On Line?
>
>

8 Jul 2005 2:08 PM

Daniel Wilson

What if you remove the Group By clause? Isn't that the simplest solution?

--
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Show quote

"marcmc" <mar***@discussions.microsoft.com> wrote in message
news:87A898D1-EBB4-48D5-AA2D-88678ACA8A22@microsoft.com...
> Please use the working example here.
> In the 1st sql statement i.) I get 3 rows.
> How can I get the sql statement in ii.) to return one row with 3 in it (as
> the count)?
>
> create table marcClmCount(
> m varchar(2),
> m1 float,
> m2 float)
>
> insert into marcClmCount values('M1', 5.00, 10.00)
> insert into marcClmCount values('M2', 5.00, 11.00)
> insert into marcClmCount values('M3', 5.00, 12.00)
> insert into marcClmCount values('M4', 0.00, 0.00)
>
> -- i.)
> SELECT m
> FROM marcClmCount
> GROUP BY m
> HAVING (ROUND(sum((m1 * m2)), 2.0)) <> 0.00
>
> -- ii.)
> SELECT count(m)
> FROM marcClmCount
> GROUP BY m
> HAVING (ROUND(sum((m1 * m2)), 2.0)) <> 0.00
>
> -- drop table marcClmCount