• Skip to main content
  • Skip to primary sidebar

Datannum

Data science for small business applications

  • Home
  • Microsoft Excel (spreadsheet) forum
    • Project: Analyze NYSE Data
  • SQL forum
    • Project: Query a Digital Music Store Database
  • Tableau forum (data visualization)
    • Project: Data Visualization Project using Tableau
  • Linear Algebra forum
    • Curated resource to learn linear algebra
  • Web development
    • WordPress forum
    • Google Support

COUNT clause example

Data Science 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

    • How to print winner January 21, 2023
    • find_min function and infinite loop when a candidate not winner in first instance January 17, 2023
    • Bool variable won and print_winner function January 12, 2023
    • True condition with while loop January 6, 2023
    • How to finally display result in tabular form of choices entered by voters December 12, 2022

    Subscribe via Email

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

    This website hosted on WP Engine

    Initiative by Digital Splendid OPC Pvt. Ltd. & Techcosec Limited