Couchbase N1QL Group by issue

SQL++
,
1

Hello All,

I am facing problem in N1QL with the group by. Please find below query for more understanding :

==========================
SELECT
t2.countryIso2,
t2.countryId,
sum(t2.clicks) AS clicks
FROM
(
SELECT
country.iso2 AS countryIso2,
country._id AS countryId,
ca.clicks AS clicks
FROM
(
SELECT
t1.campaignId as campaignId,
ca.key.v2,
sum(clicks) AS clicks
FROM
(
SELECT
ca.key.v1 AS campaignId,
ca.key.v2 ,
ca.data.clicks AS clicks
FROM defaultAna ca
WHERE (ca._type=“CAnalysis” AND ca.report = ‘campReport’)
AND ((ca.date BETWEEN 1496275200000 AND 1498521600000) OR ca.date IS NOT VALUED)
) t1 GROUP BY campaignId,ca.key.v2
) ca
JOIN default campaign ON KEYS ‘Campaign|’ || TOSTRING(ca.campaignId)
JOIN default country ON KEYS “Country|” || TOSTRING(left_campaign.country)
WHERE campaign._type = “Campaign” AND country._type=“Country”
) AS t2
GROUP BY t2.countryIso2,t2.countryId

This query returns me 125 rows

but when I add category field from the Campaign collection in SELECT like below query, then it is restricted to add the column in GROUP BY Clause.

  1. If I don’t add field in group by clause, then it gives me error “Expression must be a group key or aggregate: (t2.`campaignCategory)”
  2. If I add column in GROUP By Clause, it returns wrong number of rows in response.

==========================
SELECT
t2.countryIso2,
t2.countryId,
t2.campaignCategory, //added
sum(t2.clicks) AS clicks
FROM
(
SELECT
country.iso2 AS countryIso2,
country._id AS countryId,
campaign.category AS campaignCategory, // added, this will have value 0 / 1
ca.clicks AS clicks
FROM
(
SELECT
t1.campaignId as campaignId,
ca.key.v2,
sum(clicks) AS clicks
FROM
(
SELECT
ca.key.v1 AS campaignId,
ca.key.v2 ,
ca.data.clicks AS clicks
FROM defaultAna ca
WHERE (ca._type=“CAnalysis” AND ca.report = ‘campReport’)
AND ((ca.date BETWEEN 1496275200000 AND 1498521600000) OR ca.date IS NOT VALUED)
) t1 GROUP BY campaignId,ca.key.v2
) ca
JOIN default campaign ON KEYS ‘Campaign|’ || TOSTRING(ca.campaignId)
JOIN default country ON KEYS “Country|” || TOSTRING(left_campaign.country)
WHERE campaign._type = “Campaign” AND country._type=“Country”
) AS t2
GROUP BY t2.countryIso2,t2.countryId,t2.campaignCategory //added

Please help me to overcome from this problem.

Thanks.

2

Hi @neta,

If query uses aggregates, all non aggregates columns in projection must present in the GROUP BY. This is requirement, without that results can’t be computed and this is SQL language syntax.
It groups the rows by GROUP BY columns and with in the each group it does aggregation on group by columns and produce a row for each group. If you add t2.campaignCategory to GROUP BY number of rows will change.

The query working as expected.

3

Hi @vsr1,

Thanks for the response.

It returns duplicate rows like see the response below :
{
“campaignCategory”: 1, // It returns with campaignCategory 1
“clicks”: 0,
“countryId”: 234,
“countryIso2”: “AE”
},
{
“campaignCategory”: 0, // It returns with campaignCategory 0
“clicks”: 0,
“countryId”: 234,
“countryIso2”: “AE”
},

You can see same countryId with same GEO but two campaignCategory like 0 and 1
I want only one as I have JOIN of campaign so it should return, What I am missing in the query ?

4

You have campaignCategory in GROUP BY, so value 0 one group and value 1 another group, it returns 2 rows. If you need single row remove campaignCategory in projection, GROUP BY and click include both of them.

A GROUP BY clause works on the rows returned by a query by summarizing identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.