Query to return how many parts received that day.

[shagymoe]

Hi, thanks for your help in advance.

I need to do a query that returns how many of a given part number were received that day. Our customer sends us orders for many parts and I need to create a report which summarized how many of each part number we received for that day. I can get a list of all the parts for a certain day like this:

select part_number from st_parts where received date like '10-MAY-01' order by part_number;

but what I really need is something that tells me HOW MANY of that part number I received that day.

I'm new to sql and have a reference book which points me toward a break/compute method, but I can't make it work.....any suggestions?

 

[grega]

This should do the trick

select part_number, count(*) from st_parts where received date like '10-MAY-01'
order by part_number
group by part_number

Greg.

 

[shagymoe]

Excellent, thank you.

I think I need a major sql course. things that seem obvious are not. LIke this......

select count(*),ref_code from st1e_parts where insert_date > '11-MAY-01' and insert_date < '14-MAY-01' order by ref_code group by ref_code;

or crazy variations there of. I just can't get the hang of this.

 

[Tim1]

Hiya,

To get data between two dates is actually a lot more simple in SQL than most languages. What you need is:

To list all parts between two dates:

select ref_code, count(*) from st1e_parts where insert_date BETWEEN '11-MAY-01' AND '14-MAY-01' group by ref_code order by ref_code ;

To list parts, by date, between two dates :

select ref_code, insert_date, count(*) from st1e_parts where insert_date BETWEEN '11-MAY-01' AND '14-MAY-01' group by ref_code, insert_date order by ref_code ;

As a general rule of thumb (helps readability, doesn't affect SQL), most manuals recommend that you put your columns first, then your aggregate results (count, sum, avg etc) at the end of your select statement. You should also put group by's before order by's.

Tim