To Run without Distinct ?

[Cap2010]

hi

Is there any other way to get number of records,below is my sql and want to run without distinct, since per item there
is only one record per day.

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.

Without using distinct records are displayed
number of times as below

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 ?

 

[PHV]

Try something like this:
SELECT m.Mtrl_id,
Month(m.date) AS Mth,
Count(*) AS TotalCount
FROM MtrlTransaction m
WHERE
m.Status<>"1" AND
Year(m.date)=2004 AND
m.Mtrl_id IN (SELECT Mtrl_id FROM FROM MasterMtrl)
GROUP BY m.Mtrl_id, Month(m.date)
ORDER BY m.Mtrl_id;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Cap2010]

PHV,

No it didn't work, it has given same output

Cap2010