1

enter image description here

I would like to group rows of this table according to dates which form the start of SessionID and for each day, I would like to count how many rows there are for each set of ReqPhone values. Each set of ReqPhone values will be defined by the first four digits of ReqPhone. In other words, I would like to know how many rows there are for ReqPhone starting with 0925, 0927 and 0940, how many rows there are for ReqPhone starting with 0979, 0969 and 0955, etc etc.

I have been trying all kinds of group by and count but still haven't arrived at the right query.

Can anybody enlighten me?

Update:

In my country, the government assigns telecoms phone numbers starting with certain digits. Therefore, if you know the starting digits, you know which telecom someone is using. I am trying to count how many messages are sent each day using each telecoms.

Blair Osbay
  • 217
  • 1
  • 4
  • 13
  • Please update your question letting us know the real problem you are trying to solve, thanks. – Tim Biegeleisen Jun 20 '16 at 10:40
  • @TimBiegeleisen Thanks for solving the problem on my behalf. I had expected more of a guidance than an answer. Thanks a lot for going a mile further to help me. I have updated my question. – Blair Osbay Jun 20 '16 at 10:50
  • There are several options here. I could query my answer and use conditional aggregation to obtain counts for the various telecoms based on the signature of the first four digits. Or, the original query could be rewritten using more complex logic to directly group according to telecom. – Tim Biegeleisen Jun 20 '16 at 10:53
  • The second option will probably look like this: SELECT (CASE SUBSTRING(ReqPhone, 1, 4) WHEN '0951' THEN 'MPT' WHEN '0941' THEN 'MPT'​ WHEN '0931' THEN 'MPT' WHEN '0979' THEN 'Telenor'​​ WHEN '0977' THEN 'Telenor'​​​ END), DATEADD(DAY,0, DATEDIFF(DAY, 0, SessionID)) AS dayCreated, COUNT(*) AS tally FROM yourTable GROUP BY (CASE SUBSTRING(ReqPhone, 1, 4) WHEN '0951' THEN 'MPT' WHEN '0941' THEN 'MPT'​ WHEN '0931' THEN 'MPT' WHEN '0979' THEN 'Telenor'​​ WHEN '0977' THEN 'Telenor'​​​ END)​, DATEADD(DAY, 0, DATEDIFF(DAY, 0, SessionID)) – Blair Osbay Jun 20 '16 at 11:12

3 Answers3

3
SELECT SUBSTRING(ReqPhone, 1, 4),
       DATEADD(DAY,0, DATEDIFF(DAY, 0, SessionID)) AS dayCreated,
       COUNT(*) AS tally
FROM yourTable
GROUP BY SUBSTRING(ReqPhone, 1, 4),
         DATEADD(DAY, 0, DATEDIFF(DAY, 0, SessionID))
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks a lot Tim but I also want to group the phone numbers according to their carriers. Can this be done? – Blair Osbay Jun 20 '16 at 10:14
  • I don't see anywhere information about the carriers. Can you include this information in your question? – Tim Biegeleisen Jun 20 '16 at 10:14
  • It's like if the number starts with 0945, 0947 or 0949 etc. – Blair Osbay Jun 20 '16 at 10:16
  • My query is _already_ grouping by the first four digits on the `SessionID` column. If you need to do additional grouping on top of this, let me know what the logic is and update your question. – Tim Biegeleisen Jun 20 '16 at 10:17
  • I think I need to create sub queries. My group by will be like what they are doing [here](http://stackoverflow.com/questions/24815461/group-by-multiple-values-in-same-column) – Blair Osbay Jun 20 '16 at 10:38
1
SELECT LEFT(ReqPhone, 4),
       DATEADD(DAY,0, DATEDIFF(DAY, 0, SessionID)) AS dayCreated,
       COUNT(*) AS tally
FROM yourTable
GROUP BY LEFT(ReqPhone,4),
         DATEADD(DAY, 0, DATEDIFF(DAY, 0, SessionID))
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Rajesh Ranjan
  • 537
  • 2
  • 12
0

This will help you to calculate the count of rows group by the ReqPhone type. This query is working successfully in Oracle DB.

SELECT COUNT(SESSIONID), REQP
FROM (SELECT SESSIONID,SUBSTR(REQPHONE,1,4) AS REQP FROM SCHEMA_NAME.TABLE_NAME)
GROUP BY REQP

Note: Please use the column which is unique in the COUNT expression.

Draken
  • 3,134
  • 13
  • 34
  • 54
Sakshi Arora
  • 1
  • 1