question

abdol avatar image
abdol 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
count
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered
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]
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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]
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.