SQL Server query to solve-CodePudding

I have a database where there are multiple company names. I need to make a query that will find: company name, number of entries, date of newest entry.

Preferably order should be by the date of the latest entry (but I can sort that out even in excel)

SELECT 
    /*this is the list of companies with distinct name */
    DISTINCT(K.COMPANY) AS company_name,
    /*where I count entries*/
    (SELECT COUNT(KK.COMPANIES) FROM COMPANIES KK 
     WHERE KK.COMPANY = K.company_name) AS company_count,
    /*where I try to get newest entry date*/
    (SELECT TOP 1 ENTRY_DATE FROM COMPANIES KKK 
     WHERE KKK.COMPANY = K.company_name 
     ORDER BY KKK.ENTRY_DATE DESC) AS company_date
FROM 
    COMPANIES K 
WHERE
    K.ENTRY_DATE > '20211004 00:00:00'  
ORDER BY
    K.ID_KAMSOFT DESC

I get an error:

invalid column name

in the subqueries. Basically, the subqueries do not see data from an outer query in the condition xxx.COMPANY = K.company_name.

It should be pretty straightforward, but it doesn't work.

CodePudding user response：

You does not need to use a sub query, try a direct GROUP BY

SELECT 
    K.COMPANY AS company_name,
    COUNT(1) AS company_count,
    MAX(ENTRY_DATE) AS company_date
FROM 
    COMPANIES K 
WHERE
    K.ENTRY_DATE > '20211004 00:00:00'   
GROUP BY    
    K.COMPANY
ORDER BY
    3 DESC

CodePudding user response：

The choosen answear is obviously better, but this would work too:

(as @Nikola Markovinović stated, you simply has to use non alias name)

SELECT 
    /*this is the list of companies with distinct name */
    DISTINCT(K.COMPANY) AS company_name,
    /*where I count entries*/
    (SELECT COUNT(KK.COMPANIES) FROM COMPANIES KK 
     WHERE KK.COMPANY = K.COMPANY) AS company_count,
    /*where I try to get newest entry date*/
    (SELECT TOP 1 ENTRY_DATE FROM COMPANIES KKK 
     WHERE KKK.COMPANY = K.COMPANY
     ORDER BY KKK.ENTRY_DATE DESC) AS company_date
FROM 
    COMPANIES K 
WHERE
    K.ENTRY_DATE > '20211004 00:00:00'  
ORDER BY
    K.ID_KAMSOFT DESC