Combining 2 select statements

[eyorre]

I have a table that is set up as:

ID | NameID | Star (1 or 0)
1 | 1 | 0
2 | 1 | 0
3 | 1 | 1
4 | 2 | 1
5 | 2 | 1
6 | 3 | 0

I need to produce a recordset that will give me the NameID, the total occurances of the NameID and the number of these occurances where Star=1. (Unfortunately because the Star field is of a Bit type, I cannot just use SUM which would have made things a lot easier!) Basically I need to produce the following result:

NameID | CountOfID | CountOfStar
1 | 3 | 1
2 | 2 | 2
3 | 1 | 0

I can produce a SELECT statement that gives me CountOfID and I can produce a SELECT statement that gives me CountOfStar but I am having trouble trying to combine these statements to give me one select statement. What I have so far is:

To get the CountOfID:

SELECT tblX.NameID, Count(tblX.ID) AS CountOfID
FROM tblX
GROUP BY tblX.NameID

To get the CountOfStar:

SELECT tblX.NameID, Count(tblX.Star) AS CountOfStar
FROM tblX
WHERE tblX.Star='1'
GROUP BY tblX.NameID

So now I need to combine these so that I can pull out NameID, CountOfID and CountOfStar.

Can anyone help?

 

[JamesLean]

SELECT nameid, COUNT(*) AS id_count, SUM(CASE WHEN star = 1 THEN 1 ELSE 0 END) AS star_count
FROM tblx
GROUP BY nameid

--James

 

[eyorre]

Thanks James, this is brilliant, works a treat!

 

[JamesLean]

Actually, you could also do:

SELECT nameid, COUNT(*) AS id_count, SUM(CAST(star AS tinyint)) AS star_count
FROM tblx
GROUP BY nameid

--James

 

[eyorre]

Thanks James.

I have put in the second method and it works good.

I am now trying to create a percentage field and so I am adding this to the select statement:

(SUM(CAST(Star AS tinyint))/Count(*))*100 AS Perc

Which basically is the same as:

(CountOfStar/CountOfID)*100

It is not liking the divide though and I think it may be something to do with it rounding the numbers up or down to the nearest integer as the result is always 1 or 0. Seems to be pulling everything in correctly as when I change the divide to multiply it gives the result I would expect. Do you have any idea how I can get around this problem?

Cheers

 

[JamesLean]

Yes, because you're using integer data you can only ever get 1 or 0. You need to get it to use decimal data and the easiest way to do that is like this:

SELECT 100.0 * SUM(CAST(star AS tinyint)) / COUNT(*)
FROM t

Just by adding the decimal point to the 100 you force SQL Server to convert the other values to decimals too.

--James

 

[donutman]

Try

[Blue]SELECT[/Blue] 100. [Gray]*[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray][Fuchsia]Cast[/Fuchsia][Gray]([/Gray]Star [Blue]AS[/Blue] [Blue]tinyint[/Blue][Gray])[/Gray][Gray])[/Gray]/[Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray] [Blue]AS[/Blue] Perc

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[eyorre]

It does not seem to working it is still coming up as either 0 or 100%. My select statement now is:

SELECT 100.0*(SUM(CAST(Star AS tinyint))/Count(*)) AS Perc, RecommendedID, Count(*) AS CountOfID,
SUM(CAST(Star AS tinyint)) AS CountOfStar
FROM tblX
GROUP BY NameID

Am I doing something really dumb?

 

[JamesLean]

Try getting rid of the parentheses around the (SUM * COUNT) - you don't need them anyway. As per my example.

--James

 

[donutman]

[Blue]SELECT[/Blue] 100.0 [Gray]*[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray][Fuchsia]Cast[/Fuchsia][Gray]([/Gray]Star [Blue]AS[/Blue] [Blue]tinyint[/Blue][Gray])[/Gray][Gray])[/Gray]/[Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray] [Blue]AS[/Blue] Perc[Gray],[/Gray] 
      RecommendedID[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray] [Blue]AS[/Blue] CountOfID[Gray],[/Gray]
      [Fuchsia]SUM[/Fuchsia][Gray]([/Gray][Fuchsia]Cast[/Fuchsia][Gray]([/Gray]Star [Blue]AS[/Blue] [Blue]tinyint[/Blue][Gray])[/Gray][Gray])[/Gray] [Blue]AS[/Blue] CountOfStar 
   [Blue]FROM[/Blue] tblX
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] NameID

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

James, I just noticed that I keep repeating you! Your brain works milliseconds faster than mine.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

I'm sure it's just because you actually have some work to do - whereas I'm just really bored at the moment! ;-)

--James

 

[eyorre]

Thanks both James and Donutman. Getting rid of those brackets did the trick. I've learnt a few useful tips today! It's a good job theres some bored folk out there!