select count and group by

Results 1 to 2 of 2

Thread: select count and group by

  1. #1
    Join Date
    Dec 1969

    Default select count and group by

    Hello,<BR>I have a page that displays 3 types of images:black and white,color, and drawing. My db has a field,photo type, that determines this. I need to query the db as to the number of images for each category. I know it is select count and group by but cant find a good example of this. <BR>Also after the query is run how do you access the recordset for the number in each category?<BR>Any example or link to one is greatly appreciated.<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: select count and group by

    One way to cove both (in Access) is to use IIF like so:<BR><BR>SELECT <BR>SUM(IIF(PhotoType = &#039;BlacknWhite&#039;, 1, 0)) AS NumOfBWs,<BR>SUM(IIF(PhotoType = &#039;Color&#039;, 1, 0)) AS NumColors,<BR>SUM(IIF(PhotoType = &#039;Drawing&#039;, 1, 0)) AS NumDrawings<BR>FROM yourDB<BR><BR>This assumes you want a count of how many of each kind exist in the DB in total. If there is a qty field associated with the PhotoType, then insted of using 1 as the value for that sum, use the qty field itself, like so...<BR><BR>SUM(IIF(PhotoType = &#039;BlacknWhite&#039;, fldPhotoQty, 0)) AS NumOfBWs,<BR><BR>If you are using SQL Server, you must convert this IIF statement into a SELECT CASE statement

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts