SQL Querry Help Grouping

Get help on programming - C++, Java, Delphi, etc.
Post Reply
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

SQL Querry Help Grouping

Post by SBSP »

I have a table called

ICITEM and a table called ICILOC.

ICITEM: Is a Product item setup table that contains The Item Number, Description , ItemCategory Ect ect.....
ICILOC: Is the table that holds the stock info QTY On hand, QTY Shipped ect ect.

So im linking ICITEM and ICILOC on the Unique [ITEMNUMBER] field with an inner join.

When i return the resaults i get the following.

ItemNUMBER LOCATION QTYONHAND
ITEM001 GOOD 1
ITEM001 EXPORT 2
ITEM002 GOOD 1

So the item number is repeated in the ICILOC rows cus each location wil generate a line.

So i would like a querry that will group by item and sum by item.

So it gives me a result of.

ITEMNUMBER QTY ON HAND
ITEM001 3
ITEM002 1.

Is this possible ?
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Querry Help Grouping

Post by rustypup »

yes

Code: Select all

SELECT 
	ICITEM.ITEMNMBR, 
	ICITEM.CATEGORY, 
	SUM(ICILOC.QTYOnHand) AS ONHAND, 
	SUM(ICILOC.QTYShipped) AS SHIPPED 
FROM 
	MyCat.dbo.ICITEM LEFT JOIN 
		MyCat.dbo.ICILOC ON 
			ICILOC.ITEMNMBR=ICITEM.ITEMNMBR 
WHERE 
	<somefilter> 
GROUP BY 
	ICITEM.ITEMNMBR,  
	ICITEM.CATEGORY 
ORDER BY 
	ICITEM.CATEGORY, 
	ICITEM.ITEMNMBR 
Most people would sooner die than think; in fact, they do so - Bertrand Russel
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

Thanks.

i got it to work with your help
Last edited by SBSP on 06 Jul 2009, 14:30, edited 1 time in total.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Querry Help Grouping

Post by RuadRauFlessa »

You won't get it more efficient as well. That is a very good solution there from old rustypup. You might just want to add a nolock in there somewhere if you are only going to read data and don't want to lock the table

Code: Select all

SELECT 
   ICITEM.ITEMNMBR, 
   ICITEM.CATEGORY, 
   SUM(ICILOC.QTYOnHand) AS ONHAND, 
   SUM(ICILOC.QTYShipped) AS SHIPPED 
FROM 
   MyCat.dbo.ICITEM WITH (NOLOCK) LEFT JOIN 
      MyCat.dbo.ICILOC WITH (NOLOCK) ON 
         ICILOC.ITEMNMBR=ICITEM.ITEMNMBR 
WHERE 
   <somefilter> 
GROUP BY 
   ICITEM.ITEMNMBR,  
   ICITEM.CATEGORY 
ORDER BY 
   ICITEM.CATEGORY, 
   ICITEM.ITEMNMBR
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Querry Help Grouping

Post by rustypup »

excellent... i accept payment in whiskey... :lol:

@RuadRauFlessa: actually, the one niggle i'd have is where the sum results are returning nulls... but case statements add so much sodding overhead in MS SQL... :evil:
Most people would sooner die than think; in fact, they do so - Bertrand Russel
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

Thanks , cus latley we have loads of database locks dont wnat to be adding more.


the reason for query is.
I have to build a report for someone that does forecasting on an item database of 27K items.
Last edited by SBSP on 06 Jul 2009, 14:50, edited 3 times in total.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Querry Help Grouping

Post by RuadRauFlessa »

rustypup wrote:excellent... i accept payment in whiskey... :lol:

@RuadRauFlessa: actually, the one niggle i'd have is where the sum results are returning nulls... but case statements add so much sodding overhead in MS SQL... :evil:
well he could use

coalesce to return a default value for when the sum evaluates to a null. The only time he would get a null back from that is when there is nothing to sum. so

Code: Select all

SELECT 
   ICITEM.ITEMNMBR, 
   ICITEM.CATEGORY, 
   COALESCE(SUM(ICILOC.QTYOnHand),0) AS ONHAND, 
   COALESCE(SUM(ICILOC.QTYShipped),0) AS SHIPPED 
FROM 
   MyCat.dbo.ICITEM WITH (NOLOCK) LEFT JOIN 
      MyCat.dbo.ICILOC WITH (NOLOCK) ON 
         ICILOC.ITEMNMBR=ICITEM.ITEMNMBR 
WHERE 
   <somefilter> 
GROUP BY 
   ICITEM.ITEMNMBR,  
   ICITEM.CATEGORY 
ORDER BY 
   ICITEM.CATEGORY, 
   ICITEM.ITEMNMBR
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Querry Help Grouping

Post by RuadRauFlessa »

SBSP wrote:Thanks , cus latley we have loads of database locks dont wnat to be adding more.
Only problem with nolock is that it will return uncomitted results if the record is locked for writing. It will return the value before the lock started.

The other option you have is to use WITH (READOVER). Not sure if it is correct but I have read about the two options. The readover option skipps something if it is locked for writing.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Querry Help Grouping

Post by RuadRauFlessa »

:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

The guy needs to see

[Item number] [Description] [QTY Ordered] [Qty Backorderd] and [QTY Sold] in between a period.
By reading the line is seems simple. but its actually not possible, you cant create such a report in Crystal Reports.

The problem is the guy needs to see sales on all items whether it was sold or not.
So if the item was not sold in the specified date range then it wont show the item number cus there is no such transaction.
It comes down to the linking and no matter what join type you select when linking tables it doesnt show accurate data.

and the other problem is with the ICILOC and ICITEM table.
The iciloc table causes crystal reports to duplicate items because of the way the items are stored. (Item,Location,Qty)

So i'm trying to figure out a way to do this.
So i'm using VB to dump the data into a listview box then with another query get the Qty sold in between a date range. into a different listbox
Then problematically scan the first lisviewbox and second listviewbox for items and fill where they match and if not found enter a 0 figure. once don export to CSV.


Ive tried this before a while back then gave ,now i got a consultant to do this and the guy is also not getting it right in
crystal.

His words to me and the guy who the report is for was "Give me 30mins and i will have it ready, the guy who the report is for
Looked at me strange and said," maybe you should look at taking some lessons from this Guy."

:mrgreen: I just said "We'll see 30mins" its now more like 3 days!
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Querry Help Grouping

Post by RuadRauFlessa »

Wahahahaha.... Easy problem to solve

say you have

Code: Select all

ICITEM 
{
     ITEMNMBR int,
     CATEGORY varchar(50)
}

ICILOC
{
     ID int,
     ITEMNMBR int,
     LOCATION varcher(50),
     QTY int,
     WHEN DATETIME
}
the you should start with your main object or item which in this case is the item in ICITEM.

Code: Select all

SELECT 
     ITEMNMBR,
     CATEGORY
FROM 
     ICITEM WITH (NOLOCK)
Then add what you need. note that for your problem you don't need a join

Code: Select all

SELECT 
     ITEMNMBR,
     CATEGORY,
     COALESCE(SELECT SUM(ICILOC.QTY) FROM ICILOC WITH (NOLOCK) WHERE ICILOC.ITEMNMBR = ICITEM.ITEMNMBR AND ICILOC.WHEN BETWEEN '2009-01-01' and '2009-01-31')
FROM 
     ICITEM WITH (NOLOCK)
I think you can figure out the rest as from here it should take about 5 to 10 minutes to adapt it to your tables :wink:
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

Yes but i must join the ICILIC to ICITEM cus ICITEM contains the description field
and ICITEM dont contain the QTYonhand field
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Querry Help Grouping

Post by RuadRauFlessa »

SBSP wrote:Yes but i must join the ICILIC to ICITEM cus ICITEM contains the description field
and ICITEM dont contain the QTYonhand field
No need for a join

The above will actually iliminate the need for a join. Inner Queries FTW. They work.

Just try running that and see what you get. Or else post the full create script of both tables and I will write the complete query for you.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Querry Help Grouping

Post by rustypup »

SBSP wrote:The iciloc table causes crystal reports to duplicate items because of the way the items are stored. (Item,Location,Qty)
:? CR does a darn fine job of grouping....

by using the item master table, ICITEM, we'll assume that the ITEMNMBR is not going to repeat... so the dupe issue is solved on that score..

and, just for the hell of it:

Code: Select all

SELECT 
   ICITEM.ITEMNMBR, 
   ICITEM.DESCRIPTION, 
   ICITEM.CATEGORY, 
   SUM(ICILOC.QTYOnHand) AS ONHAND, 
   SUM(SFACTL.QTYSold) AS SOLD 
FROM 
   ((MyCat.dbo.ICITEM LEFT JOIN 
      MyCat.dbo.ICILOC ON 
         ICILOC.ITEMNMBR=ICITEM.ITEMNMBR) LEFT JOIN 
      MyCat.dbo.SALESFACTLINE SFACTL WITH(NOLOCK) ON 
         ICITEM.ITEMNMBR=SFACTLINE.ITEMNMBR) LEFT JOIN 
      MyCat.dbo.SALESFACTHEADER SFACTH WITH(NOCLOCK) ON 
         (SALESFACTHEADER.TYPE='INV') AND 
         (SALESFACTHEADER.VOID=0) AND 
         (SALESFACTHEADER.DOCNUMBR=SFACTLINE.DOCNUMBR) 
WHERE 
   YEAR(SALESFACTHEADER.DOCDATE) = <someyear> AND 
   MONTH(SALESFACTHEADER.DOCDATE) = <someperiod> AND 
   ICITEM.CATEGORY NOT IN ('NONSTOCK','HAIRYAPES','USEDTEABAG') 
GROUP BY 
   ICITEM.ITEMNMBR,  
   ICITEM.DESCRIPTION, 
   ICITEM.CATEGORY 
ORDER BY 
   ICITEM.CATEGORY, 
   ICITEM.ITEMNMBR
just to clarify - item master/on hand tables are generally low-traffic tables with a high chance of flubbing numbers if being edited, whilst SOP can produce any number of records at a given moment in time - it could be understood that most sales queries focus on historical docs, and therefore warrant a NOLOCK hint, whilst items changing category could have a sizeable impact on the end report... so, no hints provided...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

Rustypup when Cr does a "Crystal report group" its more like a SQL Order by.

Once you have the Stock on hand report going and linking on ICILOC joined to ICITEM (Wil try what RuadRauFlessa said)
You have to sum the resaults in the group header. When when you then hide the the details section it will appear like a SQL group by.

I dont care much about the lock with regards to not updated items, anyway Once you post and order and everything ships
IC dont get updated anyway only when you run a day end it gets updated we run about 6 dayends a day
So those items will not be far out also this is mostly done at month end when the fiscal period is still locked.
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

Will give your query a go
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Querry Help Grouping

Post by rustypup »

SBSP wrote:Rustypup when Cr does a "Crystal report group" its more like a SQL Order by.
are you suppressing the group's line detail?...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

rustypup wrote:
SBSP wrote:Rustypup when Cr does a "Crystal report group" its more like a SQL Order by.
are you suppressing the group's line detail?...
Not suppressed but hidden (in CR language)
I dont have to but thats the only way i know of to get the totals in line with the item number to be exported to excel
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

can i not rather create 2 views one that gets on hand and the other one that get on BO then
incrystal link both to ICITEM then put in a sub report for sales ?
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Querry Help Grouping

Post by rustypup »

SBSP wrote:only way i know of to get the totals in line with the item number to be exported to excel
ahh... :lightdawns:...

are you running MS SQL?... if so, have you glanced at reporting services yet?
SBSP wrote:can i not rather create 2 views one that gets on hand and the other one that get on BO
and how would you go about declaring the view date bounds? (i suppose you could do this using GETDATE()....)

if you can pull the BO numbers into the same query, why not?... or is the BO inclusion proving tricky?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

I'm unsure how to approach this so apologies if i'm was being unclear.

Yes its SQL2005 i'm not sure what Reporting services are, is it the MS version of Crystal ?



If so no i cant cus everything here is build round crystal reports already and the company wont buy another reporting application as crystal Pro was already 5K per license
Our ERP clients also includes crystal runtimes. So its best if i stick with it and i dont think having a new reporting program will justify 1 report :lol:

I have SQL2005 Stored procs, Views , tables and Crystal reports at my disposal to make this report.


And to your last post.

I could try and pull in BO orders but BO's is listed per transaction in order entry OEORDH and ORORDD which is the header and detail. i only need the detail. And i sum the detail field QTYBCKRD where its > 0

But ive now already created 2 views one for BO and one for QTY on hand and if i link the 2 to the ICITEM table on ITMENO using a right outer join.
I get the 27K items we have when i do a sum so it works perfectly.
On the items where there is no Back order, its just blank which is fine cus you can always do a find and replace in Excel to make the spaces 0 (Not ideal but i should be fine)

I just need to somehow include the sales side now, I'm thinking of using a Sub Report which joins to ICITEM's ITEMNO field also using a right outer join. I dont think crystal can pass date parameters to a view.

But i think i'm on track now.



RuadRauFlessa i got you PM, thanks its appreciated will try what i have here if it doesnt work i will let you know.
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Querry Help Grouping

Post by rustypup »

SBSP wrote:is it the MS version of Crystal ?
sort of... but it allows you to schedule report runs, which is a blessing around month end... simply schedule and forget...
SBSP wrote:the company wont buy another reporting application as crystal Pro was already 5K per license
i stand to be corrected, but SQL 2005 includes RS for free... depending on version...
SBSP wrote:i only need the detail. And i sum the detail field QTYBCKRD where its > 0
does the detail table include a date field to bind to?... if not, i'm sure the header table does... this means you don't end up selecting all historical data, which is what a standard view would do...

if the sales document number is present in all tables, including the sales tables, is there any reason why you're breaking the data up?
I dont think crystal can pass date parameters to a view.
if you're going to go with the view, at least restrain the records ..

Code: Select all

SELECT yadayada 
FROM yadayada 
WHERE YEAR(SOME_DATE)=YEAR(GETDATE()) AND MONTH(SOME_DATE) = (MONTH(GETDATE())
or something along those lines..
Most people would sooner die than think; in fact, they do so - Bertrand Russel
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

Done!

You guys Rock!

I created the 2 views like so.

Code: Select all

USE [COMPANY]
GO
/****** Object:  View [dbo].[ForecastQTyONHAND]    Script Date: 07/07/2009 09:07:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[ForecastQTyONHAND]
AS
SELECT     ICITEM.ITEMNO, SUM(ICILOC.QTYONHAND) AS QTYONHAND
FROM         dbo.ICITEM AS ICITEM INNER JOIN
                      dbo.ICILOC AS ICILOC ON ICITEM.ITEMNO = ICILOC.ITEMNO
GROUP BY ICITEM.ITEMNO
and

Code: Select all

USE [COMPANY]
GO
/****** Object:  View [dbo].[ForeCastQtyonBO]    Script Date: 07/07/2009 09:07:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[ForeCastQtyonBO]
AS
SELECT     ITEM, SUM(QTYBACKORD) AS SUMQTYBO
FROM         dbo.OEORDD AS OEORDD
WHERE     (QTYBACKORD > 0)
GROUP BY ITEM
Right outer joined the 2 views item fields to the ICITEM's item field using a Right outer join.

Then in CR built the report to show item number item description , Qty on hand, qty on Bo.

Then added the OESHDT table which is the sales history table (Detail). also right outer join to ICITEM item field
Put in a parameter , created 1 formula that says.

Code: Select all

if {OESHDT.TRANDATE} > {?Date From} then
    if  {OESHDT.TRANDATE} < {?Date To} then
{OESHDT.QTYSOLD}
else
0
Grouped the report by item hide the details section put in a sumary on QTY sold and moved it to be in the same line
as the group header then also, moved the qty bo, qty on hand and desc to be inline with the group header (Item group)

Run export into excel and figures match up 100%

Would it be fair for me to ask our money back that we spent on the consultant ?
I dont mind the call out but the consultant's fees is already at 8 grand ?
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Querry Help Grouping

Post by RuadRauFlessa »

SBSP, he didn't solve your problem. So just throw it into management's faces that they said something bad about your work and they will handle the rest. Just do it subtly.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
SBSP
Registered User
Posts: 3124
Joined: 09 May 2006, 02:00
Location: Centurion

Re: SQL Querry Help Grouping

Post by SBSP »

Yip think i'm going to do that.

There is one thing that really get's to me, thats when
Someone ignorantly ask you to do something and they dont really how much work is involved or really cares
and they make it out to be simple.

And when you explain to them you cant cus of this and that, they look at you strange when you get technical.
I mean how do i explain database table names and linking them together

This user couldive just run 3 different reports and used a vlookup in excel i'm sure
to get the results.

But at least its done. I'm not too happy using SQL Views cus when the next ERP upgrade comes its just another
nag that has to be upgraded. Cus of some table change. :roll:
Post Reply