Number of Records without distinct - duplicate ?

[Cap2010]

hi
What's the error, below SQL finds number of records

SELECT distinct
m1.Mtrl_id,
Month(m2.date) AS Mth,
Count(Year(m2.Date)) AS TotalCount
FROM MasterMtrl m1
INNER JOIN MtrlTransaction m2 ON
m1.Mtrl_id = m2.Mtrl_id
WHERE
(m2.Status<>"1") AND
(Year(m2.date)=2004))
GROUP BY m1.Mtrl_id, Month(m2.date)
ORDER BY m1.Mtrl_id;


On giving the word distinct in SQL it gives exactly what I am looking for
and without distinct it repeats the same number 30 times each month and 29 times for Feb.

Want to do without using Distinct in SQL, as the data is not repeating also. As the records per item is only one for each day

Without using distinct records are displayed
number of times i.e.

Without Distinct
ItemNo Mth Count
A0001 1 30
A0001 1 30
A0001 1 30
A0001 1 30
A0001 2 35
A0001 2 35

A0002 1 20
A0002 1 20
A0002 2 15
A0002 2 15

With Distinct
ItemNo Mth Count
A0001 1 30
A0001 2 35

A0002 1 20
A0002 2 15

How to do without distinct

 

[lupins46]

'On giving the word distinct in SQL it gives exactly what I am looking for'

This is usually enough for most people-)

Why are you looking for a different solution?