Link to home
Create AccountLog in
Avatar of johnkainn
johnkainn

asked on

Distinct - total

I have a table with the columns  Id, MyNum
I would like to select 1) distinct MyNum 2) how many times that number appears in the table.
For example:  MyNum=3, Total=20 (MyNum=3 appear 20 times in the table)
how is best to do that?
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of johnkainn
johnkainn

ASKER

Thank you. If instead of MyNum column I have a datetime column. I would like to return distinct Hour:Min, for example: '11:22'
I use "convert(varchar(5), StartDate, 8) as H" to select Hour:Min.
How do I group by H.
select mynum, count(1) as total
from tbl
group by mynum
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
try this
SELECT SUBSTRING(CONVERT(varchar(50),mynum,8),1,5),COUNT(*) cnt 
  FROM your_table
 GROUP BY SUBSTRING(CONVERT(varchar(50),mynum,8),1,5)

Open in new window

or

SELECT SUBSTRING(CONVERT(varchar(5),mynum,8),1,5),COUNT(*) cnt
  FROM your_table
 GROUP BY SUBSTRING(CONVERT(varchar(5),mynum,8),1,5)
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account