getting distinct result with few more conditions in mysql

Question

Hai i have some sample data

 bookId     | bookPnr       | bookDate   | bookFullName | bookMobile | bookEmail         | bookSource
 9876543210 | BPT1100000000 | 2018-11-18 | User 1       | 9876543210 | test@gmail.com    | Redbus
 9876543211 | BPT1100000001 | 2017-11-18 | User 2       | 9876543211 | testOne@gmail.com | Redbus
 9876543212 | BPT1100000002 | 2017-11-18 | User 3       | 9876543214 | testtwo@gmail.com | TicketGoose

I need a result like

Mobile      | 2018 | 2017 | 2016 | Redbus | TicketGoose | total

9876543210  |  2   | 3    | 6    | 2      | 2           | 11
9876543211  |  1   | 1    | 1    | 2      | 1           | 3

So i need distinct mobile numbers based on year and source I did query something like,

SELECT count(bookId), bookMobile, bookDate, bookSource FROM `booking_info` 
GROUP by bookMobile, MONTH(bookDate), bookSource ORDER BY bookMobile DESC

Is it possible to do it with single query or we have to use PHP Any suggetions will be really appreciated.

MySQL <> SQL server. Please specify which RDBMS you are using. — Madhur Bhaiya, Nov 17 '18 at 07:25
add a proper data sample .. coherent with your expected result — ScaisEdge, Nov 17 '18 at 07:25
How many distinct BookSources are there? single digit? tens? more than tens? or do you just want `Redbus | TicketGoose` ? — Paul Maxwell, Nov 17 '18 at 07:32
You are showing values for 2016 and 2017, but there is no `bookDate` for either of these years in the sample data. Please ensure that the sample data should match the expected output. — Madhur Bhaiya, Nov 17 '18 at 07:33
@MadhurBhaiya i have only bookDate it contains year month and day i have to filter year from that only — Pattatharasu Nataraj, Nov 17 '18 at 07:35
Please go through this link once: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) — Madhur Bhaiya, Nov 17 '18 at 07:36
Consider handling issues of data display in application code — Strawberry, Nov 17 '18 at 07:37

score 1 · Accepted Answer · answered Nov 17 '18 at 07:37

You can use "conditional aggregates" to "pivot" your data. Basically this means placing a case expression inside an aggregation function. Here I have used COUNT():

SELECT
    bookMobile
  , count(case when year(bookDate) = 2016 then 1 end) as `2016`
  , count(case when year(bookDate) = 2017 then 1 end) as `2017`
  , count(case when year(bookDate) = 2018 then 1 end) as `2018`
  , count(case when bookSource = 'Redbus' then 1 end) as Redbus
  , count(case when bookSource = 'TicketGoose' then 1 end) as TicketGoose
FROM booking_info
GROUP BY
    bookMobile
ORDER BY
    bookMobile DESC

getting distinct result with few more conditions in mysql

1 Answers1