asked
how to count count child rows
Table master No Date A0300000003 10/5/10 10:55 AM A0300000002 10/5/10 10:52 AM A0300000001 10/5/10 10:49 AM A0300000004 10/5/10 11:11 AM Table Child No Product ID Qty Ccode A0300000001 FC00001 1 FC A0300000001 SN007 1 SN A0300000002 FC00001 2 FC A0300000003 SN007 1 SN A0300000004 FC00001 2 FC Result Expected Ccode Totals FC 2 SN 1
the title of your question includes the work "count" (twice) but the results you say you want seem to indicated you want the max Qty. Can you clarify please?
This may include the answer you are looking for but you will need to explain your problem more to get a better answer CREATE TABLE mstr ( Num CHAR(11) , Dte DATETIME -- column_name data_type,... ) CREATE TABLE chld ( Num CHAR(11) , productid VARCHAR(8) , qty INT , ccode CHAR(2)-- column_name data_type,... ) INSERT [dbo].[mstr] ( [Num], [Dte] ) VALUES ( 'A0300000003', '10/5/10 10:55' ), ( 'A0300000002', '10/5/10 10:52' ), ( 'A0300000001', '10/5/10 10:49' ), ( 'A0300000004', '10/5/10 11:11' ) INSERT [dbo].[chld] ( [Num], [productid], [qty], [ccode] ) VALUES ( 'A0300000001', 'FC00001', 1, 'FC' ), ( 'A0300000001', 'SN007', 1, 'SN' ), ( 'A0300000002', 'FC00001', 2, 'FC' ), ( 'A0300000003', 'SN007', 1, 'SN' ), ( 'A0300000004', 'FC00001', 2, 'FC' ) SELECT COUNT(*) AS count_ccode , MAX(qty) AS max_ccode , [c].[ccode] FROM [dbo].[chld] AS c GROUP BY [c].[ccode] DROP TABLE [dbo].[mstr] DROP TABLE [dbo].[chld]
hi Thanks for your reply. Apologize for incomplete info provided by me. There is another table category CCode Name _____________ FN Fast Food SN Snacks LQ Liquid Food my requirement is to calculate the number of bills for a specified date. I have to calculate total number of bills for each CCode. for example bill no 'A0300000001' child has to items which is calculated as mixed bills and billno 'A0300000002' has 1 item under ccode 'FC'. expected result Ccode Totals _____________ FC 2 [(A0300000002, A0300000004)] SN 1 [(A0300000003)] 1 [(A0300000001)] in the above expected result [bills calculated]
8 People are following this question.
