How use n1ql to return multiple count?

SQL++
,
1

I have some docs in my bucket like this :
Scenario doc :

{
  "Form": [
    {
      "IsArchived": false,
      "TimeStamp": "2017-09-26T14:46:24.414Z",
      "Title": "Iform 2",
      "customerId": "Customer_dd4ced5b-7ff7-450f-88bd-642d162afbaa",
      "docType": "Form",
      "id": "7ffe22f8-a5a6-483c-9683-82e5e27b439f"
    }
  ],
  "IsActived": true,
  "IsArchived": false,
  "IsExpired": false,
  "Title": "Iscenario 2",
  "customerId": "Customer_dd4ced5b-7ff7-450f-88bd-642d162afbaa",
  **"docType": "Scenario",**
  "id": "Scenario_322a64e4-430e-4f71-a903-e8f7426ff3eb",
  "managerRelId": 57
}

and Mission doc:

{
  "AddPointPermission": true,
  "Description": "dcfdzc",
  "Done": false,
  "IsActive": true,
  "IsArchived": false,
  "IsExpired": false,
  "ManagerName": "undefined undefined",
  "Mandetory": false,
  **"Status": "Done",**
  "TimeStamp": "2017-12-16T10:58:14.952Z",
  "Title": "Mahdi Mission 32",
  "customerId": "Customer_dd4ced5b-7ff7-450f-88bd-642d162afbaa",
  **"docType": "Mission",**
  "endDate": "2017/11/24 00:00",
  "endDateTime": 1511481600000,
  "id": "Mission_7ef3eaee-5458-451c-84ea-5f1bb612ce2",
  "loginRelId": "loginRelId",
  "managerValue": 57,
  **"scenarioId": "Scenario_322a64e4-430e-4f71-a903-e8f7426ff3eb",**
  "startDate": "2017/11/16 00:00",
  "startDateTime": 1510790400000,
  "userRelId": 55
}

,

{
  "AddPointPermission": true,
  "Description": "dcfdzc",
  "Done": false,
  "IsActive": true,
  "IsArchived": false,
  "IsExpired": false,
  "ManagerName": "undefined undefined",
  "Mandetory": false,
  **"Status": "Doing",**
  "TimeStamp": "2017-12-15T10:58:14.952Z",
  "Title": "Mahdi Mission 12",
  "customerId": "Customer_dd4ced5b-7ff7-450f-88bd-642d162afbaa",
  **"docType": "Mission",**
  "endDate": "2017/11/29 00:00",
  "endDateTime": 1511481600000,
  "id": "Mission_7ef3eaee-5458-451c-84ea-5f1bb612c66",
  "loginRelId": "loginRelId",
  "managerValue": 57,
  **"scenarioId": "Scenario_322a64e4-430e-4f71-a903-e8f7426ff3eb",**
  "startDate": "2017/11/18 00:00",
  "startDateTime": 1510790400000,
  "userRelId": 55
}

,
ā€¦

In my app I have multiple scenario and each scenario has multiple mission. In missionā€™s docs I have a scenarioId and Status and all of my docs have docType property which here is Scenario and Mission . Missionā€™s Status has three value {ā€˜Doneā€™,ā€˜Doingā€™,ā€˜Activeā€™} and for each scenario I need sum of all missions and sum of Done Missions.
I want a result like this:

{
ā€œscenarioIdā€: ā€œScenario_322a64e4-430e-4f71-a903-e8f7426ff3ebā€,
ā€œDoneMissionsā€:24,
ā€œAllMissionForThisScenarioā€:45
{

How should I write query for that??

2 
SELECT scenarioId,
      COUNT(Status) AS AllMissionForThisScenario,
      SUM(CASE WHEN Status = "Done" THEN 1 ELSE 0 END) AS DoneMissions
FROM bucketname
WHERE docType = "Mission"
GROUP BY scenarioId;
4 Likes