question

trainAsbeast avatar image
trainAsbeast asked

SUM OF COUNTS?

Hi, I have this SQL code for table with data - CREATE TABLE "users" ( user_id INTEGER, male INTEGER ); INSERT INTO "users" (user_id, male) VALUES ('4050', '1'), ('6254', '0'), ('8260', ''), ('4050', '1'); /* SEX REPRESENTS man - 1 /woman - 0 */ **How do I get % of known male? It is 3/4 - 75%. But what SQL code to get a table with the percentage?** Thanks!
sqlcountsum
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
I'm not sure what format you need but here's one way to get what you ask for and a bit more. WITH ctePreAgg AS ( SELECT Male = SUM(male) ,Total = COUNT(*) FROM dbo.users ) SELECT Male ,MalePct = CONVERT(DECIMAL(4,1),Male*100.0/Total) ,Female = Total-Male ,FemalePct = CONVERT(DECIMAL(4,1),(Total-Male)*100.0/Total) ,Total FROM ctePreAgg ;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Need to create an extra page all_male. There was syntax error saying no such column TOTAL in the code you provided... Thanks tho
0 Likes 0 ·
tzvikl avatar image
tzvikl answered
select (select cast(count(*) as real)from ##users where male = 1)/ count(*) from ##users
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.