I want to create a report which displays the stats for data in a sql server table.The table columns I am interested in are searchTerm, dateadded. Now I want to get the count of records for each searchterm but also display the earliest and latest dateadded for that searchterm. for example:
我想要创建一个报表,该报表显示sql server表中的数据统计信息。我感兴趣的表列是searchTerm, dateadd。现在,我想获取每个searchterm的记录计数,但也要显示为该searchterm添加的最早和最新的数据。例如:
select searchterm, count(*) as recCount from SearchTable order by searchterm.
will just give me the counts so I just need the dates and distinct records only.
只会给我计数,所以我只需要日期和不同的记录。
2 个解决方案
#1
3
Try this:
试试这个:
SELECT
searchterm,
MIN(dateadded) AS minDateAdded,
MAX(dateadded) AS maxDateAdded,
count(*) as recCount
FROM SearchTable
GROUP BY searchterm
They GROUP BY will ensure that each row is distinct.
他们将确保每一行都是不同的。
#2
1
You can use MIN and MAX functions, plus the GROUP BY clause
可以使用最小和最大值函数,加上GROUP BY子句
SELECT searchterm, MIN(dateadded) AS earliest, MAX(dateadded) AS latest, COUNT(*) as recCount
FROM SearchTable
GROUP BY searchterm
ORDER BY searchterm
#1
3
Try this:
试试这个:
SELECT
searchterm,
MIN(dateadded) AS minDateAdded,
MAX(dateadded) AS maxDateAdded,
count(*) as recCount
FROM SearchTable
GROUP BY searchterm
They GROUP BY will ensure that each row is distinct.
他们将确保每一行都是不同的。
#2
1
You can use MIN and MAX functions, plus the GROUP BY clause
可以使用最小和最大值函数,加上GROUP BY子句
SELECT searchterm, MIN(dateadded) AS earliest, MAX(dateadded) AS latest, COUNT(*) as recCount
FROM SearchTable
GROUP BY searchterm
ORDER BY searchterm