Count problems

I have sqldatasources that are used to display information in a gridview. The data that is being collected comes from 3 different tables. From the one table called comments all i want is a count of all comment table rows that have a common id with a row in the files table. The select query i have right now only returns results that have a comment count of more than 0, i want entries with a comment count of 0 to be found... here is what i have so far (i am a sql noob :( )

SELECT dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias, COUNT(dbo.Comments.FID) AS comcount

FROM dbo.Files INNER JOIN dbo.Users ON dbo.Files.UID = dbo.Users.UID INNER JOIN dbo.Comments ON dbo.Files.FID = dbo.Comments.FID

WHERE (dbo.Files.Company = @.Company)

GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID, dbo.Users.Name, dbo.Users.AliasThere is probably an easier way to do this but try this.. I think it might work..

SELECT dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID AS Expr1, dbo.Users.Name, dbo.Users.Alias,
SUM(CASE WHEN dbo.Comments.FID IS NOT NULL THEN 1 ELSE 0 END) AS comcount

FROM dbo.Files
LEFT JOIN dbo.Users ON dbo.Files.UID = dbo.Users.UID
LEFT JOIN dbo.Comments ON dbo.Files.FID = dbo.Comments.FID

WHERE (dbo.Files.Company = @.Company)

GROUP BY dbo.Files.FID, dbo.Files.UID, dbo.Files.FileName, dbo.Files.Date, dbo.Files.tType, dbo.Files.numPoints, dbo.Files.numDlds, dbo.Files.Confirmation, dbo.Users.UID, dbo.Users.Name, dbo.Users.Aliassql

Count of no. of characters

Hi,
Could anyone please let me know how many characters can we display in a single row in Crystal Report XI?
Thanks.It depends on the size of the page. Can you give us more informations on what you are trying to do

COUNT many but display just once?

Hi, I have a table that I insert a member's country into every time
someone signs up. What I'd like to do is pull information from the DB
such that I can see each country and the number of users from each.
For example:

Argentina 10
Brazil 5
Canada 3

I'm having trouble writing the SQL for this...any suggestions?

Thanks,

Erik
"Erik Lautier" <lautier@.gmail.comwrote in message
news:1177283677.045638.109480@.o5g2000hsb.googlegro ups.com...

Quote:

Originally Posted by

Hi, I have a table that I insert a member's country into every time
someone signs up. What I'd like to do is pull information from the DB
such that I can see each country and the number of users from each.
For example:
>
Argentina 10
Brazil 5
Canada 3

W/o schema it's impossible to say what you want for sure but something like

create table membership
(
country varchar(20),
signup varchar(20)
);

insert into membership values ('Argentina', 'member 1')
insert into membership values ('Argentina', 'member 2')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 2')
insert into membership values ('Brazil', 'member 3')
insert into membership values ('Canada', 'member 1')

select country, count(signup) from membership group by country

SELECT COUNTRY, COUNT(signup) from membership group by country

Quote:

Originally Posted by

>
I'm having trouble writing the SQL for this...any suggestions?
>
Thanks,
>
Erik
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That did the trick. Thanks!

On Apr 22, 8:23 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

"Erik Lautier" <laut...@.gmail.comwrote in message
>
news:1177283677.045638.109480@.o5g2000hsb.googlegro ups.com...
>

Quote:

Originally Posted by

Hi, I have a table that I insert a member's country into every time
someone signs up. What I'd like to do is pull information from the DB
such that I can see each country and the number of users from each.
For example:

>

Quote:

Originally Posted by

Argentina 10
Brazil 5
Canada 3

>
W/o schema it's impossible to say what you want for sure but something like
>
create table membership
(
country varchar(20),
signup varchar(20)
);
>
insert into membership values ('Argentina', 'member 1')
insert into membership values ('Argentina', 'member 2')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 2')
insert into membership values ('Brazil', 'member 3')
insert into membership values ('Canada', 'member 1')
>
select country, count(signup) from membership group by country
>
SELECT COUNTRY, COUNT(signup) from membership group by country
>
>
>

Quote:

Originally Posted by

I'm having trouble writing the SQL for this...any suggestions?

>

Quote:

Originally Posted by

Thanks,

>

Quote:

Originally Posted by

Erik

>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html