How do I add a column with the same value for each row?

I was asked to do a search against our database and to explain the number of clients in the 5 different age ranges. I know I can run the same query five times. However, i was hoping to be able to create one query that would do the whole job.

To my knowledge, I need to do 5 queries and then use "UNION" 开发者_开发知识库to connect the different queries.

The issue is that for each query i want to add a column that says "age range" and then put some text to name the group.

The SQL I came up with was:

SELECT     COUNT(CLIENT_ID) AS Client_Count, "0 to 19" AS [Age Group]
FROM         dbo.CLIENTS
WHERE     (DATEDIFF(year, DOB, GETDATE()) BETWEEN 0 AND 19)

While I hoped this would work, it expects the 0 to 19 to be a column. How would I let it know that I'm just trying to pass it a fixed value?

Once I get this to work, they would then like that I the values as a percentage of the total. Any help is appreciated.

This should work. Try single quotes.

SQL uses single quotes to denote string text. Seems like it would be able to interpret double quotes, but there you have it.

The following should give you a good starting point I think:

;with AgeRanges(Low, High) as (
    select 0, 19
    union select 20, 39
    union select 40, 59
    union select 60, 79
    union select 80, 1000
)

select
    count(CLIENT_ID) as Client_Count,
    convert(varchar, Low) + ' to ' + convert(varchar, High) as [Age Group]
from
    dbo.CLIENTS
    inner join AgeRanges
        on  DATEDIFF(year, DOB, getdate()) BETWEEN Low and High

Change your speach marks. E.g:

SELECT '0 to 19' AS [Age Group]

In Full:

SELECT     COUNT(CLIENT_ID) AS Client_Count, '0 to 19' AS [Age Group]
FROM         dbo.CLIENTS
WHERE     (DATEDIFF(year, DOB, GETDATE()) BETWEEN 0 AND 19)

Not sure if I got it right, but if you want the results in columns, you can do something like this:

SELECT
  [0 to 19]  = COUNT(CASE WHEN DATEDIFF(year, DOB, GETDATE()) BETWEEN  0 AND 19 THEN 1 END),
  [20 to 39] = COUNT(CASE WHEN DATEDIFF(year, DOB, GETDATE()) BETWEEN 20 AND 39 THEN 1 END),
  …
FROM dbo.Clients