CODEWITHSUNDEEP
sqlgoogle-bigquery
Matthew Hooson
Matthew Hooson

Reputation: 43

How to SUM the COUNT list in SQL

Here you can see I want to count all page hits, then score the sum. There is no error at all until I add in 'COUNT(hits.type = 'PAGE') AS PV_yo' at which point it complains about ' hits.page.pageTitle,' not being grouped. I am new to this and stumped..

#standardSQL
SELECT
  pageTitle,
  contentGroup1,
  CASE
      WHEN SUM(pv_yo)<=400 THEN 1 * 0.4
      WHEN  SUM(pv_yo) <=800 THEN 2 * 0.4
      WHEN  SUM(pv_yo) <=1625 THEN 3 * 0.4
      WHEN  SUM(pv_yo) <=3250 THEN 4* 0.4
      WHEN  SUM(pv_yo)  <=6500 THEN 5 * 0.4
      WHEN  SUM(pv_yo) <=13000 THEN 6 * 0.4
      WHEN  SUM(pv_yo) <=26000 THEN 7 * 0.4
      WHEN  SUM(pv_yo) <=52000 THEN 8 * 0.4
      WHEN  SUM(pv_yo)<=104000 THEN 9 * 0.4
      WHEN  SUM(pv_yo)>104000 THEN 10 * 0.4
    ELSE
    0
  END
     AS PV_score,

FROM 
 ( SELECT
    hits.page.pageTitle,
    hits.contentGroup.contentGroup1,
    COUNT(hits.type = 'PAGE') AS PV_yo

  FROM
    `datasetname.ga_sessions_*` AS GA,
    UNNEST(GA.hits) AS hits,
    UNNEST(hits.customDimensions) AS CD
  WHERE
    CD.index = 3
    AND hits.contentGroup.contentGroup1 != '(not set)' )

GROUP BY
  pageTitle,
  contentGroup1
ORDER BY
  AVG_PV DESC

Upvotes: 2

Views: 513

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You need aggregation, so I assume the subquery should be:

SELECT hits.page.pageTitle,
       hits.contentGroup.contentGroup1,
       COUNTIF(hits.type = 'PAGE') AS PV_yo
FROM `datasetname.ga_sessions_*` AS GA CROSS JOIN
     UNNEST(GA.hits) AS hits CROSS JOIN
     UNNEST(hits.customDimensions) AS CD
WHERE CD.index = 3 AND
      hits.contentGroup.contentGroup1 <> '(not set)' 
GROUP BY hits.page.pageTitle,
         hits.contentGroup.contentGroup1;

Note that I changed the COUNT() to COUNTIF() -- under the assumption that you are using BigQuery. In any case, COUNT() is uninteresting because that counts the number of non-NULL values rather than the number of true values.

Upvotes: 2

Related Questions