• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Datannum

Data science and web development for small business applications

  • Home
  • Microsoft Excel (spreadsheet) forum
    • Project: Analyze NYSE Data
  • SQL forum
    • Project: Query a Digital Music Store Database
  • Data Visualization
    • Project: Data Visualization Project using Tableau
    • IBM – Cognos Analytics/Planning Analytics/Cognos Controller with Watson
  • Linear Algebra forum
    • Curated resource to learn linear algebra
  • Hire Us/Support

COUNT clause example

Data Science/Business Analytics for Small Business Applications › Forums › SQL › COUNT clause example

Tagged: COUNT clause in SQL

  • This topic is empty.
Viewing 1 post (of 1 total)
  • Author
    Posts
  • April 20, 2022 at 11:12 am #134
    Admin
    Keymaster

      Here is the problem:

      Question 9

      All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

      Run Query: Find the albums with 12 or more tracks.

      Here is my tentative solution:

      SELECT 
      Name, AlbumId, TrackId, COUNT (AlbumId > 11) AS countalbumid
      FROM 
      Tracks
      GROUP BY
      AlbumId, TrackId
      
      

      Source: https://www.coursera.org/learn/sql-for-data-science%5B/caption%5D

      Also, I could see Album Id 1 has 10 tracks as per first page. How do I count and get output of 10 against Album Id 1?

      It will help to have clue on how to summarize AlbumId 1 against Tracks. For instance, AlbumId 1 has 10 Tracks. How to count 10 under AlbumId 1. That way counting will I understand yield result. AlbumId 1 occurs 10 times in column 1. So, it is associated with 10 tracks. Same for AlbumId 2 and so on.

      Also, it is not clear to me what COUNT(AlbumId) doing against each row with an output of 1.


      Reply


      Your grouping is wrong as there are non-aggregates in SELECT missing in GROUP BY. Better to refer here rather than taking short cuts.

      However, the main issue is — COUNT (AlbumId > 11)

      The comparison results in TRUE/FALSE — and how do you count values that are TRUE or FALSE?

      (some databases, like MySQL, convert TRUE and FALSE to 1 and 0, so you’d be counting integers, but that’s not the problem here)

      Albumid is a column value, so do you really want to count the albums that have an id that’s greater than 11?


      The remedy is HAVING clause

      SELECT AlbumId, COUNT(AlbumId) AS countalbum
      FROM Tracks
      GROUP BY AlbumId
      HAVING countalbum > 11
      
      

      Also, it is not clear to me what COUNT(AlbumId) doing against each row with an output of 1.

      you queried for counts for every track on every album

      GROUP BY AlbumId, TrackId
      

      so for each track on each album, you get a count

      and since each track exists on its album only once, that count is 1


      “Also, I could see Album Id 1 has 10 tracks as per first page. How do I count and get output of 10 against Album Id 1?”

      easy, just group by album

      how many tracks in an album? you count them for each album

      so you need GROUP BY AlbumId

      SELECT AlbumId, COUNT(AlbumId) AS countalbum FROM Tracks GROUP BY AlbumId HAVING countalbum > 11
      
      
    • Author
      Posts
    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.
    Log In

    Primary Sidebar

    profile for Techcosec Limited on Stack Exchange, a network of free, community-driven Q&A sites

    RSS CS50’s Introduction to Computer Science by Harvard University on Edx

    • Are pairs array and lock array similar in structure after execution of sort_pairs function? Is my base case okay with pairs array or do I need to modify with referencing to lock array? May 27, 2023
    • Recursion in lock-pairs function while drawing analogy with sum of natural numbers: What will be the recursive case May 25, 2023
    • Understanding recursive function with Tideman May 24, 2023
    • More explanation for the Tideman project May 22, 2023
    • Why the below for loop not leading to segmentation fault or garbage value and instead giving correct output May 21, 2023

    Subscribe via Email

    Enter your email address to subscribe and receive notifications of new posts by email.

    This website hosted on WP Engine

    Footer

    Editor’s choice

    Developer jobs remote full time
    • Terms
    • Privacy
    • Disclaimer