count multiple distinct columns

I want to build query to return how many rows are in this query:
select distinct c1, c2 from t1

But SQL won't accept this syntax:
select count (distinct c1, c2) from t1

Does someone know how to count multiple distinct columns? Thanks.

--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.One method is to use a derived table:

SELECT COUNT(*)
FROM (
SELECT DISTINCT c1, c2
FROM t1) AS t1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dean" <noreply@.fakeaddress.com> wrote in message
news:cd1o36$aho$1@.news01.intel.com...
> I want to build query to return how many rows are in this query:
> select distinct c1, c2 from t1
> But SQL won't accept this syntax:
> select count (distinct c1, c2) from t1
> Does someone know how to count multiple distinct columns? Thanks.
>
> --
> Disclaimer: This post is solely an individual opinion and does not speak
on
> behalf of any organization.|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:8D_Ic.1568
> One method is to use a derived table:
> SELECT COUNT(*)
> FROM (
> SELECT DISTINCT c1, c2
> FROM t1) AS t1

Thanks! I was trying
SELECT COUNT(*) FROM (SELECT DISTINCT c1, c2 FROM t1)
but it wouldn't work without the "AS t1" at the end.

--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.|||Try this out..

SELECT COUNT(*) FROM (select distinct c1, c2 from t1)T

COUNT FUNCTION ON MULTIPLE COLUMNS

I have a database that contains a column for UnitName , BeginDate and
EndDate.

I want to pass two parameters (@.BeginDate and @.EndDate) and retrieve a
table of values

that include UnitName along with Counts for each UnitName.

SELECT UnitName, COUNT(BeginDate) AS Start
(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @.BeginDate AND
@.EndDate)
FROM Table
WHERE BeginDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY UnitName
ORDER BY UnitName

This works. But when I try to add another count by using a subselect I
get an error dealing with GROUP BY not including the column in my
subselect.

How is the best way to Count two columns using Group By.k4 wrote:

Quote:

Originally Posted by

I have a database that contains a column for UnitName , BeginDate and
EndDate.
>
I want to pass two parameters (@.BeginDate and @.EndDate) and retrieve a
table of values
>
that include UnitName along with Counts for each UnitName.
>
SELECT UnitName, COUNT(BeginDate) AS Start
(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @.BeginDate AND
@.EndDate)
FROM Table
WHERE BeginDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY UnitName
ORDER BY UnitName
>
This works. But when I try to add another count by using a subselect I
get an error dealing with GROUP BY not including the column in my
subselect.
>
How is the best way to Count two columns using Group By.

Assuming the BeginDates are always <= the corresponding EndDates, you
can do:

SELECT UnitName,
COUNT(CASE WHEN BeginDate
BETWEEN @.BeginDate AND @.EndDate THEN 1 END) AS BeginDate,
COUNT(CASE WHEN EndDate
BETWEEN @.BeginDate AND @.EndDate THEN 1 END) AS EndDate
FROM tbl
WHERE BeginDate <= @.EndDate
AND EndDate >= @.BeginDate
GROUP BY UnitName
ORDER BY UnitName;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Count from multiple tables

I have 4 tables
One is a user table and the other three contain records for the users. They all have a USERNAME column
I would like to get a count of records for each table grouped by USERNAME

My output would be:
username,totalFrom1,totalFrom2,totalFrom3

Thanks For the help!Mybe something like:

select
username,
isnull(t1.ttlfrom1,0) ttlfrom1,
isnull(t2.ttlfrom2,0) ttlfrom2,
isnull(t3.ttlfrom3,0) ttlfrom3
from <users> u
left join
(
select username, count(username) ttlfrom1
from
<t1>
group by username
) t1 on t1.username = u.username
left join
(
select username, count(username) ttlfrom2
from
<t2>
group by username
) t2 on t2.username = u.username
left join
(
select username, count(username) ttlfrom3
from
<t3>
group by username
) t3 on t3.username = u.username

|||Thanks! I would never have figured that one out on my own, but I see how it works.
Thanks again
Greg