CODEWITHSUNDEEP
sqlsql-servert-sqlssms
raw
raw

Reputation: 13

How do I select the most frequent value for a specific month and display this value as well as the amount of times it occurs?

I am struggling with a TSQL query and I'm all out of googling, so naturally I figured I might as well ask on SO.

Please keep in mind that I just began trying to learn SQL a few weeks back and I'm not really sure what rules there are and how you can and can not write your queries / sub-queries.

This is what I have so far:

Edit: Updated with DDL that should help create an example, also commented out unnecessary "Client"-column.

CREATE TABLE NumberTable
(
Number varchar(20),
Date date
);

INSERT INTO NumberTable (Number, Date)
VALUES
('55512345', '2015-01-01'),
('55512345', '2015-01-01'),
('55512345', '2015-01-01'),
('55545678', '2015-01-01'),
('55512345', '2015-02-01'),
('55523456', '2015-02-01'),
('55523456', '2015-02-01'),
('55534567', '2015-03-01'),
('55534567', '2015-03-01'),
('55534567', '2015-03-01'),
('55534567', '2015-03-01'),
('55545678', '2015-03-01'),
('55545678', '2015-04-01')

DECLARE
    --@ClientNr AS int,
    @FromDate AS date,
    @ToDate AS date

--SET @ClientNr = 11111
SET @FromDate = '2015-01-01'
SET @ToDate = DATEADD(yy, 1, @FromDate)

SELECT
    YEAR(Date) AS [Year],
    MONTH(Date) AS [Month],
    COUNT(Number) AS [Total Count]
FROM
    NumberTable
WHERE
    --Client = @ClientNr
    Date BETWEEN @FromDate AND @ToDate
    AND Number IS NOT NULL
    AND Number NOT IN ('888', '144')
GROUP BY MONTH(Date), YEAR(Date)
ORDER BY [Year], [Month]

With this I am getting the Year, Month and Total Count.

I'm happy with only getting the top 1 most called number and count each month, but showing top 5 is preferable.

Heres an example of how I would like the table to look in the end (having the months formatted as JAN, FEB etc instead of numbers is not really important, but would be a nice bonus):

╔══════╦═══════╦═════════════╦═══════════╦══════════╦═══════════╦══════════╗
║ Year ║ Month ║ Total Count ║ #1 Called ║ #1 Count ║ #2 Called ║ #2 Count ║
╠══════╬═══════╬═════════════╬═══════════╬══════════╬═══════════╬══════════╣
║ 2016 ║ JAN   ║       80431 ║ 555-12345 ║    45442 ║ 555-94564 ║    17866 ║
╚══════╩═══════╩═════════════╩═══════════╩══════════╩═══════════╩══════════╝

I was told this was "easily" done with a sub-query, but I'm not so sure...

Upvotes: 1

Views: 100

Answers (4)

Serg
Serg

Reputation: 22811

artm's query corrected (PARTITION) and the last step (pivoting) simplified.

with data AS
(select '2016-01-01' as called, '111' as number
union all select '2016-01-01', '111'
union all select '2016-01-01', '111'
union all select '2016-01-01', '222'
union all select '2016-01-01', '222'
union all select '2016-01-05', '111'
union all select '2016-01-05', '222'
union all select '2016-01-05', '222')
, ordered AS (
select called
, number
, count(*) cnt
, ROW_NUMBER() OVER (PARTITION BY called ORDER BY COUNT(*) DESC) rnk
from data
group by called, number)
select called, total = sum(cnt)
, n1= max(case rnk when 1 then number end)
, cnt1=max(case rnk when 1 then cnt end)
, n2= max(case rnk when 2 then number end)
, cnt2=max(case rnk when 2 then cnt end)
 from ordered 
 group by called

EDIT Using setup provided by OP

WITH ordered AS(
   -- compute order
   SELECT 
    [Year] = YEAR(Date) 
    , [Month] = MONTH(Date)  
    , number
    , COUNT(*) cnt
    , ROW_NUMBER() OVER (PARTITION BY YEAR(Date), MONTH(Date) ORDER BY COUNT(*) DESC) rnk
    FROM NumberTable
    WHERE Date BETWEEN @FromDate AND @ToDate
        AND Number IS NOT NULL
        AND Number NOT IN ('888', '144')
    GROUP BY YEAR(Date), MONTH(Date), number
)
-- pivot by order
SELECT [Year], [Month] 
    , total = sum(cnt)
    , n1 = MAX(case rnk when 1 then number end)
    , cnt1 = MAX(case rnk when 1 then cnt end)
    , n2 = MAX(case rnk when 2 then number end)
    , cnt2 = MAX(case rnk when 2 then cnt end)
    -- n3, cnt3, .... 
FROM ordered 
GROUP BY [Year], [Month];

Upvotes: 0

artm
artm

Reputation: 8584

Try this, doesn't have to be CTE but I used it to populate data, you can extend it to include 3rd, 4th etc.

;with data AS
(select '2016-01-01' as called, '111' as number
union all select '2016-01-01', '111'
union all select '2016-01-01', '111'
union all select '2016-01-01', '222'
union all select '2016-01-01', '222')
, ordered AS (
select called
, number
, count(*) cnt
, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rnk
from data
group by called, number)
SELECT distinct *
FROM (SELECT DATENAME(month, called) mnth FROM ordered) AS mnth,
(SELECT number MostCalledNumber FROM ordered WHERE rnk = 1) AS MostCalledNumber,
(SELECT cnt MostCalledTimes FROM ordered WHERE rnk = 1) AS MostCalledTimes,
(SELECT number SecondMostCalledNumber FROM ordered WHERE rnk = 2) AS SecondMostCalledNumber,
(SELECT cnt SecondMostCalledTimes FROM ordered WHERE rnk = 2) AS SecondMostCalledTimes

Upvotes: 0

Mike Miller
Mike Miller

Reputation: 16575

Interesting one this, I believe you can do it with a CTE and PIVOT but this is off the top of my head... This may not work verbatim

WITH Rollup_CTE
AS
(
    SELECT Client,MONTH(Date) as Month, YEAR(Date) as Year, Number, Count(0) as Calls, ROW_NUMBER() OVER (PARTITION BY Client,MONTH(Date) as SqNo, YEAR(Date), Number ORDER BY COUNT(0) DESC)
    from NumberTable 
        WHERE Number IS NOT NULL AND Number NOT IN ('888', '144')
    GROUP BY Client,MONTH(Date), YEAR(Date), Number
)
SELECT * FROM Rollup_CTE Where SqNo <=5

You may then be able to pivot the data as you wish using PIVOT

Upvotes: 1

Nikolay Fedorov
Nikolay Fedorov

Reputation: 387

This query help you:

IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL DROP TABLE #Test;

CREATE TABLE #Test(Number INT NOT NULL)

INSERT INTO #Test(Number)
VALUES(1),(2),(3),(1)

SELECT TOP 1 WITH TIES
    Number
FROM (
SELECT DISTINCT
    Number
    , COUNT(*) OVER(PARTITION BY Number) AS cnt
FROM #Test) AS T
ORDER BY cnt DESC

I have used TOP 1 WITH TIES for case when max count exists for several values.

Upvotes: 0

Related Questions