Count records of a column after group by in SQL

Question

I'm running this query:

SELECT "uname","sip",count(1) as cnt FROM "random_data_date" GROUP BY "uname","sip";

To get something like this:

+------------+-----------------+--------------+
|   uname    |    sip          | cnt          |
+------------+-----------------+--------------+
| root       | 172.17.165.60   | 1            |
| root       | 172.17.53.124   | 2            |
| root       | 172.28.184.39   | 3            |
| root       | 192.168.207.7   | 1            |
| root       | 192.168.245.110 | 1            |
| user1      | 172.17.165.60   | 1            |
| user1      | 172.24.85.227   | 10           |
| user1      | 172.25.14.184   | 2            |
| user2      | 172.16.194.151  | 1            |
| user2      | 172.16.248.88   | 1            |
| user2      | 172.16.9.55     | 1            |
| user2      | 172.17.165.60   | 2            |
| user2      | 172.17.234.122  | 1            |
| user2      | 172.17.53.124   | 1            |
+------------+-----------------+--------------+

Is there a way so that I can make it add up the number of sips for each username, and add up the cnts of them, so the output is like this:

+------------+-----------------+--------------+
|   uname    |    sipcnt       | cnt          |
+------------+-----------------+--------------+
| root       | 5               | 8            |
| user1      | 3               | 13           |
| user2      | 6               | 7            |
+------------+-----------------+--------------+

I'm using Apache Phoenix to query Apache HBase.

I found this question that is similar but I don't know how would I apply the answers to my situation.

Answer 1

Would be something like this:

SELECT 
    "uname",
    count(distinct "sip"),
    count("sip") as cnt 
FROM 
    "random_data_date" 
GROUP BY 
    "uname"
;

Answer 2

SELECT "uname",
       Count(distinct "sip") as "sipcnt",
       count(1) as cnt 
FROM "random_data_date" 
GROUP BY "uname";

Answer 3

Running this query will give you the results you want:

SELECT DISTINCT(uname), COUNT(sip), SUM(cnt) FROM random_data_date GROUP BY uname