CODEWITHSUNDEEP
sqldatabaset-sql
Oryx
Oryx

Reputation: 302

T-SQL: Build New Table from Result Set

First off: total SQL newbie here. I unfortunately had this thrust on me on a deadline. :/ My apologies if this is a dumb question.

So I have a DB with a view that has various columns. It has two columns I'm interested in: UserID and Age.

The view lists all reports for a user.

We I need to do is create a new table/view from the existing view that lists each unique user, and then breaks down their total documents into various age groups. So the columns would look something like:

User || # rprts <14 days || # rprts 14-17 days || # rprts 18-28 days || # rprts 29-44 days || # rprts 45+ days

I know I can get the set of all the users I want with:

SELECT DISTINCT [UserID] FROM [DB].[dbo].[DB_View]

And I can run the following and get one row of what I want for the new table/view by hardcoding a UserID #:

SELECT DISTINCT 

    [UserID],
    (SELECT COUNT(*) FROM [DB].[dbo].[DB_View] WHERE (Age < 14) AND UserID = '9999') AS "Less Than 14 Days",
    (SELECT COUNT(*) FROM [DB].[dbo].[DB_View] WHERE (Age BETWEEN 14 AND 17) AND UserID = '9999') AS "14 to 17 Days",
    (SELECT COUNT(*) FROM [DB].[dbo].[DB_View] WHERE (Age BETWEEN 18 AND 28) AND UserID = '9999') AS "18 to 28 Days",
    (SELECT COUNT(*) FROM [DB].[dbo].[DB_View] WHERE (Age BETWEEN 29 AND 44) AND UserID = '9999') AS "29 to 44 Days",
    (SELECT COUNT(*) FROM [DB].[dbo].[DB_View] WHERE (Age >= 45) AND UserID = '9999') AS "Over 45 Days"

FROM [DB].[dbo].[DB_View]
WHERE UserID = '9999'

The result set of users is about ~600 rows, and the view usually has about 6,000-15,000 rows. My initial thought was just get my result set of users, then store each user to a parameter, and then pass that into the query where I've hardcoded a User ID. Sadly, I can't even figure out the SQL to do that. (And I'm guessing there's a much more efficient way to do this if you know your SQL....) I've seen stuff on using cursors and stuff, but I don't really understand it that well.

Any ideas?

Upvotes: 0

Views: 331

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this with a simple group by, with the CASE statement for conditional sums by age:

SELECT [UserID],
       SUM(case when Age < 14 then 1 else 0 end) AS "Less Than 14 Days",
       SUM(case when Age BETWEEN 14 AND 17 then 1 else 0 end) as "14 to 17 Days",
       SUM(case when Age BETWEEN 18 AND 28 then 1 else 0 end) as "18 to 28 Days",
       SUM(case when Age BETWEEN 29 AND 44 then 1 else 0 end) as "29 AND 44 Days",
       SUM(case when Age BETWEEN 29 AND 44 then 1 else 0 end) as "29 AND 44 Days",
       SUM(case when Age >= 45 then 1 else 0 end) as "Over 45 Days"
from [DB].[dbo].[DB_View] 
where  UserID = '9999'
group by userID

Upvotes: 0

lc.
lc.

Reputation: 116448

If I'm understanding you right:

SELECT [UserID],
    COUNT(CASE WHEN Age < 14 THEN 1 ELSE NULL END) AS [Less Than 14 Days],
    COUNT(CASE WHEN Age BETWEEN 14 AND 17 THEN 1 ELSE NULL END) AS [14 to 17 Days],
    COUNT(CASE WHEN Age BETWEEN 18 AND 28 THEN 1 ELSE NULL END) AS [18 to 28 Days],
    COUNT(CASE WHEN Age BETWEEN 29 AND 44 THEN 1 ELSE NULL END) AS [29 to 44 Days],
    COUNT(CASE WHEN Age >= 45 THEN 1 ELSE NULL END) AS [Over 45 Days]

FROM [DB].[dbo].[DB_View]
GROUP BY [UserID]

Upvotes: 3

Related Questions