x

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 
more ▼

asked Sep 22, 2012 at 12:23 PM in Default

avatar image

abdol
0 2 2 4

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?

Sep 22, 2012 at 03:51 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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]
more ▼

answered Sep 22, 2012 at 04:00 PM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

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]

Sep 24, 2012 at 04:50 AM abdol
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53

asked: Sep 22, 2012 at 12:23 PM

Seen: 1156 times

Last Updated: Sep 24, 2012 at 08:36 AM

Copyright 2018 Redgate Software. Privacy Policy