Create a new column from Select statement then combining it with another Select statement in one pro-CodePudding

I want to count data from a SQL table Archive_Hits and create a column programStarts that displays that data. Then I want to take the programStarts column and combine it with another Select statement to display one data table.

Here is my Procedure:

ALTER PROCEDURE [dbo].[testing2] @startdate datetime, @enddate datetime
AS
BEGIN

SELECT Archive_Hits.inst_id, Count(Archive_Hits.type) AS programStarts 
FROM Archive_Hits 
WHERE Archive_Hits.type<>'New' AND Archive_Hits.accessed_on BETWEEN  cast ( @startdate as date)  and cast ( @enddate as date)
GROUP BY Archive_Hits.inst_id


SELECT email,product_market,install_code, programStarts
FROM (Archive_ProgUsers INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id) 
INNER JOIN Archive_Hits AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
ORDER BY programStarts DESC

Please note that I tried using UNION and UNION ALL methods, but that doesn't work if I don't have the same number of columns in each statement.

Also, The second select statement returns an error because it doesn't recognize the newly created column from the first Select statement.

============================== Here is a sample result table result

CodePudding user response：

you need to provide sample data but I think this is what you need:

SELECT
    email,
    product_market,
    install_code,
    Count(case when hitsCount.type <> 'New' and hitsCount.accessed_on BETWEEN cast(@startdate as date) and cast(@enddate as date) then 1 end) over (partition by inst_id) as programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN Archive_Hits AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
ORDER BY programStarts DESC

ok then you need this instead:

SELECT
    email,
    product_market,
    install_code,
    programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
    SELECT inst_id 
        , Count(case when hitsCount.type <> 'New' and hitsCount.accessed_on BETWEEN cast(@startdate as date) and cast(@enddate as date) then 1 end) as programStarts
    From Archive_Hits
    GROUP BY Archive_Hits.inst_id 
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
ORDER BY programStarts DESC