I have used Base SAS for analysis for a while and it was really great.. everything is easy just with a simple command.. I am sure it's not the same in SQL Server but I need some help on how to start with the following:
I have a field called call_country and another field called call_minute. Each call will be saved with the destination country and the total number of minutes..
and I want to run a query to see what are the TOP frequent destinations in this format:
United States - Count: 420 - Total Minues: 12,345
It should be easy in SQL too.
SELECT call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
|||If you want use the top, you can do this:
--1.based on total minutes
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY TotalMinutes DESC
--2. based on total count
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY tCount DESC
--3.based on total count and use total minutes as tie break.
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY tCount DESC, TotalMinutes DESC
--4.based on total minutes and use total count as tie break.
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY TotalMinutes DESC, tCount DESC
