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