Member Avatar for shamsidah

hello.. i have a table name paintshop and have 4 fields
for example

table:paintshop
bodyNo | defect_code | qty | date
58786859 | FM | 9 | 2011-11-29
58786859 | BB | 1 | 2011-11-29
58786859 | TN | 4 | 2011-11-29
58786860 | RN | 7 | 2011-11-29
58786851 | LM | 9 | 2011-11-29

I want to count the bodyNo only for 2011-11-29 and display the numbers of bodyNo = 3 ( do not count the repeating bodyNo)

  • 2 Contributors
  • 2 Replies
  • 106 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by shamsidah

Dani AI

Generated

Good suggestion from @-|x — counting distinct body numbers for the target date is the right idea and will return 3 for the sample rows in 's post (58786859, 58786860, 58786851). A few practical points and cautions to make that solution robust and efficient.

If the column named date is actually a DATETIME, a simple equality check against a date string will miss rows with non‑midnight times. Use a date-only comparison or a date-range that covers the whole day so all rows from that calendar day are included. Also, date is a common identifier that can cause confusion; consider using a clearer name like event_date or created_at, or always quote the identifier to avoid accidental parsing issues.

On performance: distinct counts over large tables can be expensive. A composite index on the date column plus bodyNo lets the server restrict to the day first and then deduplicate efficiently. If counts are needed for many days at once, aggregate by the date column to get per-day counts in a single pass instead of running one query per date.

Data hygiene matters: ensure bodyNo values are stored consistently (same type, no stray whitespace, preserved leading zeros when intended). DISTINCT ignores NULLs, so explicitly handle NULL or empty values if they should count. Verifying results by listing the unique bodyNo values for the day will quickly reveal formatting or type issues that look like duplicates but are not.

Recommended Answers

All 2 Replies

Member Avatar for |-|x 
SELECT count(DISTINCT bodyNo) FROM paintshop WHERE `date`='2011-11-29'
Edited by |-|x because: n/a
Member Avatar for shamsidah 
SELECT count(DISTINCT bodyNo) FROM paintshop WHERE `date`='2011-11-29'

dear Hearth, thanks you very much for ur helps...:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.