Архив метки: GROUP BY

Группировка данных по дням, по месяцам или по годам в MSSQL

По дням:

select convert(char(8),Date_and_Time,112) mydate, count(calls_id)
from calls
group by convert(char(8),Date_and_Time,112)
order by mydate

По месяцам:

select convert(char(6),Date_and_Time,112) mydate, count(calls_id)
from calls
group by convert(char(6),Date_and_Time,112)
order by mydate

По годам:

select convert(char(4),Date_and_Time,112) mydate, count(calls_id)
from calls
group by convert(char(4),Date_and_Time,112)
order by mydate

mssql как узнать, сколько места занимают таблицы в базе

Добавить комментарий

Не моё, нашёл где-то в инете, но проверил, код рабочий, запрос очень помог

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id  = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE  'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    -- t.Name
    UsedSpaceKB desc