Reputation: 238
I would like to get the name of the entity which has MAX count in the dataset and would also like to get the count of the same.
SELECT
STORE_TYPE AS 'FREQUENT CHANNEL',
COUNT(STORE_TYPE) AS 'TOTAL TRANSACTIONS'
FROM
TRANSACTION_INFO
GROUP BY
STORE_TYPE
This returns the count of the STORE_TYPE
along with the TOTAL_TRANSACTIONS
.
Now, I would like to fetch the STORE_TYPE
along with TOTAL TRANSACTIONS
which has performed the best (MAX).
Thanks in advance!
Upvotes: 0
Views: 109
Reputation: 1271231
If you want one row, the logic would be group by
to calculate the count. Then order by
and some way of limiting the result to the top:
SELECT STORE_TYPE, COUNT(*) AS cnt
FROM TRANSACTION_INFO
GROUP BY STORE_TYPE
ORDER BY cnt DESC
FETCH FIRST 1 ROW ONLY;
In the event of ties, this only returns one arbitrary row with the highest value. The best way to handle ties depends on the database.
FETCH FIRST 1 ROW ONLY
is the ISO/ANSI standard for limiting the result set to one row. Your database may use a different syntax for this.
I also strongly discourage you from using single quotes for column aliases. Use names that don't need to be escaped -- that is the best advise. Otherwise, use the escape mechanism for your database, typically double quotes, backticks, or square braces.
EDIT:
The solution in SQL Server that includes ties is:
SELECT TOP (1) STORE_TYPE, COUNT(*) AS cnt
FROM TRANSACTION_INFO
GROUP BY STORE_TYPE
ORDER BY cnt DESC;
If you only want one row, remove the WITH TIES
.
Upvotes: 5
Reputation: 48875
The other answers work well, as long as there's a single store_type
that is doing best. If you also want to account for multiple ones tied in first place you should use:
with s (t, c) as (
select store_type, count(*)
from transaction_info group by store_type
)
select
t as 'FREQUENT CHANNEL',
c as 'TOTAL TRANSACTIONS'
from s
where c = (select max(c) from s)
This query will show you one or more rows.
Upvotes: 1
Reputation: 13836
You can simply ORDER BY the max and then limit to a single row.
SELECT
STORE_TYPE AS 'FREQUENT CHANNEL',
COUNT(STORE_TYPE) AS 'TOTAL TRANSACTIONS'
FROM TRANSACTION_INFO
GROUP BY STORE_TYPE
ORDER BY 'TOTAL TRANSACTIONS' DESC
LIMIT 1
The way to LIMIT depends on the SQL dialect.
Upvotes: 2