Grouping

[civilwarjunky]

I have writen my querry and it runs fine. I am new to sql and have not have much experiance in grouping.

Iwould like my querry have the persons name show only once, and when mulitipal programs show up I would like that to show up once with a total number of that program. Same is true with the item nume. It with past a peace of my resualts so you can see.

Select Location.name as 'Location', Contact.firstname+','+lastname as 'Name',contact.suffix,
contact.isactive,Program.name,programitem.name,quantity

from location,contact,orderitem,Program,Programitem,ddlistitem,ddlist

Where Contact.id=orderitem.contactid
and contact.primarycontacttypeid=ddlistitem.id
and Location.id= orderitem.locationid
and Program.id=programitem.programid
and Programitem.id=Orderitem.programitemid
and contact.isactive=1
and contact.suffix ='MD'
and contact.primarycontacttypeid=5
and orderitem.createdon between '2004-07-01' and '2005-01-14'
rder by Contact.lastname,orderitem.createdon


NAME PROGRAM ITEM QTY
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50
Alex,Abbassi MD Pregnancy Pregnancy Spanish 50

 

[checkai]

do you want this?

Select  Location.name as 'Location', Contact.firstname+','+lastname as 'Name',contact.suffix,
contact.isactive,Program.name,programitem.name,quantity

from location,contact,orderitem,Program,Programitem,ddlistitem,ddlist

Where Contact.id=orderitem.contactid
and contact.primarycontacttypeid=ddlistitem.id
and Location.id= orderitem.locationid
and Program.id=programitem.programid
and Programitem.id=Orderitem.programitemid
and contact.isactive=1
and contact.suffix ='MD' 
and contact.primarycontacttypeid=5
and orderitem.createdon between '2004-07-01' and '2005-01-14'
group by Location.name as 'Location', Contact.firstname+','+lastname as 'Name',contact.suffix,
contact.isactive,Program.name,programitem.name
order by Contact.lastname,orderitem.createdon

 

[civilwarjunky]

I would like it to look like this
name program Item qty
Alex,Abbassi MD Pregnancy Pregnancy Spanish 600

 

[SonOfEmidec1100]

When grouping you must group by all fields you are using in the select and order by clauses, except those you are summing,maxing or whatever. Other comments,

Use INNER JOIN structure, rather than the WHERE clause for this linking tables.

Table ddlist listed but not used

Your date format can potentially cause problems, always use
mm-dd-yyyy or preferably dd-MMM-yyyy

Anyway heres what I think you need, may not be 100% but should get you started

SELECT     Contact.firstname + ',' + Contact.lastname AS [Name], Contact.suffix, Program.[name] AS Program, 
           Programitem.[name]  AS Item, SUM(quantity) AS Qty
FROM       Contact INNER JOIN ddlistitem ON Contact.primarycontacttypeid = ddlistitem.ID
                   INNER JOIN OrderItem ON Contact.ID = OrderItem.contactID
                   INNER JOIN Location ON OrderItem.locationid = Location.ID
                   INNER JOIN Programitem
                   INNER JOIN Program ON Programitem.programid = Program.ID ON OrderItem.programitemid = Programitem.ID
WHERE     (Contact.isactive = 1) AND (Contact.suffix = 'MD') AND (Contact.primarycontacttypeid = 5)
          AND (OrderItem.createdon BETWEEN '01-JUL-2004' AND '14-JAN-2004')
GROUP BY Contact.firstname, Contact.lastname, Contact.suffix,
         Program.[name], Programitem.[name], OrderItem.createdon
ORDER BY Contact.lastname, OrderItem.createdon

 

[checkai]

ah yes, i forgot the sum

Select  Location.name as 'Location', Contact.firstname+','+lastname as 'Name',contact.suffix,
contact.isactive,Program.name,programitem.name,SUM(quantity)

from location,contact,orderitem,Program,Programitem,ddlistitem,ddlist

Where Contact.id=orderitem.contactid
and contact.primarycontacttypeid=ddlistitem.id
and Location.id= orderitem.locationid
and Program.id=programitem.programid
and Programitem.id=Orderitem.programitemid
and contact.isactive=1
and contact.suffix ='MD' 
and contact.primarycontacttypeid=5
and orderitem.createdon between '2004-07-01' and '2005-01-14'
group by Location.name as 'Location', Contact.firstname+','+lastname as 'Name',contact.suffix,
contact.isactive,Program.name,programitem.name
order by Contact.lastname,orderitem.createdon