SQL Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

Below is the query Ive been running which gives me a summary of all
shipments (indivcards) to individual stores (accounts) for one chosen month.

SELECT DESIHIST.ACCOUNT, DESIHIST.CARDSHIP, DESIHIST.MONTH, DESIHIST.SEASON,
PRCEHEAD.WHOLESAL AS 'PACKCOST', PRCEHEAD.RETAIL, PRCEHEAD.UNITCONT,
STCKHEAD.PRICE, STCKHEAD.PRODUCT, (DESIHIST.CARDSHIP*PRCEHEAD.UNITCONT) AS
'INDIVCARDS', ROUND((DESIHIST.CARDSHIP*PRCEHEAD.WHOLESAL),2) AS 'TOTALCOST'
FROM CMCompanyA.dbo.DESIHIST DESIHIST, CMCompanyA.dbo.PRCEHEAD PRCEHEAD,
CMCompanyA.dbo.STCKHEAD STCKHEAD
WHERE DESIHIST.SEASON = STCKHEAD.SEASON AND DESIHIST.DESIGN =
STCKHEAD.DESIGN AND STCKHEAD.SEASON = PRCEHEAD.SEASON AND STCKHEAD.PRODUCT =
PRCEHEAD.PRODUCT AND STCKHEAD.PRICE = PRCEHEAD.PRICE AND
((DESIHIST.ACCOUNT='002') AND (DESIHIST.CARDSHIP>0) AND
(DESIHIST.MONTH='200604') OR (DESIHIST.ACCOUNT='003') AND
(DESIHIST.CARDSHIP>0) AND (DESIHIST.MONTH='200604'))

What I need to do is aggregate this up to account level so rather than for
example seeing 2000 records returned which shows shipments and costs at the
lowest level what I need to do is summarise this by account which will give
me 2 records but which adds up all the shipments by account.

For example store 1 cardsshipped (sum of indivcards); store 2 cardshipped;
store 3 cardshipped etc.

Hope this can be followed without seeing it in a spreadsheet.

Thanks

Reggiee
 
Thanks Steve

Did try that and it didnt like it - will try and copy over the sql incl the
group by bit and see if theres something silly in there

Reggiee

[MVP] S.Clark said:
Use the GROUP BY clause to summarise

Select ACCOUNT, SUM([whatever]) from Tablename GROUP BY ACCOUNT


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Reggiee said:
Hi All

Below is the query Ive been running which gives me a summary of all
shipments (indivcards) to individual stores (accounts) for one chosen
month.

SELECT DESIHIST.ACCOUNT, DESIHIST.CARDSHIP, DESIHIST.MONTH,
DESIHIST.SEASON,
PRCEHEAD.WHOLESAL AS 'PACKCOST', PRCEHEAD.RETAIL, PRCEHEAD.UNITCONT,
STCKHEAD.PRICE, STCKHEAD.PRODUCT, (DESIHIST.CARDSHIP*PRCEHEAD.UNITCONT) AS
'INDIVCARDS', ROUND((DESIHIST.CARDSHIP*PRCEHEAD.WHOLESAL),2) AS
'TOTALCOST'
FROM CMCompanyA.dbo.DESIHIST DESIHIST, CMCompanyA.dbo.PRCEHEAD PRCEHEAD,
CMCompanyA.dbo.STCKHEAD STCKHEAD
WHERE DESIHIST.SEASON = STCKHEAD.SEASON AND DESIHIST.DESIGN =
STCKHEAD.DESIGN AND STCKHEAD.SEASON = PRCEHEAD.SEASON AND STCKHEAD.PRODUCT
=
PRCEHEAD.PRODUCT AND STCKHEAD.PRICE = PRCEHEAD.PRICE AND
((DESIHIST.ACCOUNT='002') AND (DESIHIST.CARDSHIP>0) AND
(DESIHIST.MONTH='200604') OR (DESIHIST.ACCOUNT='003') AND
(DESIHIST.CARDSHIP>0) AND (DESIHIST.MONTH='200604'))

What I need to do is aggregate this up to account level so rather than for
example seeing 2000 records returned which shows shipments and costs at
the
lowest level what I need to do is summarise this by account which will
give
me 2 records but which adds up all the shipments by account.

For example store 1 cardsshipped (sum of indivcards); store 2 cardshipped;
store 3 cardshipped etc.

Hope this can be followed without seeing it in a spreadsheet.

Thanks

Reggiee
Click to expand...
Click to expand...
 
As long as you understand to replace the word whatever with whatever it is
that you need to sum.

Reggiee said:
Thanks Steve

Did try that and it didnt like it - will try and copy over the sql incl
the
group by bit and see if theres something silly in there

Reggiee

[MVP] S.Clark said:
Use the GROUP BY clause to summarise

Select ACCOUNT, SUM([whatever]) from Tablename GROUP BY ACCOUNT


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Reggiee said:
Hi All

Below is the query Ive been running which gives me a summary of all
shipments (indivcards) to individual stores (accounts) for one chosen
month.

SELECT DESIHIST.ACCOUNT, DESIHIST.CARDSHIP, DESIHIST.MONTH,
DESIHIST.SEASON,
PRCEHEAD.WHOLESAL AS 'PACKCOST', PRCEHEAD.RETAIL, PRCEHEAD.UNITCONT,
STCKHEAD.PRICE, STCKHEAD.PRODUCT, (DESIHIST.CARDSHIP*PRCEHEAD.UNITCONT)
AS
'INDIVCARDS', ROUND((DESIHIST.CARDSHIP*PRCEHEAD.WHOLESAL),2) AS
'TOTALCOST'
FROM CMCompanyA.dbo.DESIHIST DESIHIST, CMCompanyA.dbo.PRCEHEAD
PRCEHEAD,
CMCompanyA.dbo.STCKHEAD STCKHEAD
WHERE DESIHIST.SEASON = STCKHEAD.SEASON AND DESIHIST.DESIGN =
STCKHEAD.DESIGN AND STCKHEAD.SEASON = PRCEHEAD.SEASON AND
STCKHEAD.PRODUCT
=
PRCEHEAD.PRODUCT AND STCKHEAD.PRICE = PRCEHEAD.PRICE AND
((DESIHIST.ACCOUNT='002') AND (DESIHIST.CARDSHIP>0) AND
(DESIHIST.MONTH='200604') OR (DESIHIST.ACCOUNT='003') AND
(DESIHIST.CARDSHIP>0) AND (DESIHIST.MONTH='200604'))

What I need to do is aggregate this up to account level so rather than
for
example seeing 2000 records returned which shows shipments and costs at
the
lowest level what I need to do is summarise this by account which will
give
me 2 records but which adds up all the shipments by account.

For example store 1 cardsshipped (sum of indivcards); store 2
cardshipped;
store 3 cardshipped etc.

Hope this can be followed without seeing it in a spreadsheet.

Thanks

Reggiee
Click to expand...
Click to expand...
Click to expand...
 
Thanks again,

So if there are say 5 fields in the select statement do I need 5 groupings
or do I just group the field I want to aggregate?

[MVP] S.Clark said:
As long as you understand to replace the word whatever with whatever it is
that you need to sum.

Reggiee said:
Thanks Steve

Did try that and it didnt like it - will try and copy over the sql incl
the
group by bit and see if theres something silly in there

Reggiee

[MVP] S.Clark said:
Use the GROUP BY clause to summarise

Select ACCOUNT, SUM([whatever]) from Tablename GROUP BY ACCOUNT


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi All

Below is the query Ive been running which gives me a summary of all
shipments (indivcards) to individual stores (accounts) for one chosen
month.

SELECT DESIHIST.ACCOUNT, DESIHIST.CARDSHIP, DESIHIST.MONTH,
DESIHIST.SEASON,
PRCEHEAD.WHOLESAL AS 'PACKCOST', PRCEHEAD.RETAIL, PRCEHEAD.UNITCONT,
STCKHEAD.PRICE, STCKHEAD.PRODUCT, (DESIHIST.CARDSHIP*PRCEHEAD.UNITCONT)
AS
'INDIVCARDS', ROUND((DESIHIST.CARDSHIP*PRCEHEAD.WHOLESAL),2) AS
'TOTALCOST'
FROM CMCompanyA.dbo.DESIHIST DESIHIST, CMCompanyA.dbo.PRCEHEAD
PRCEHEAD,
CMCompanyA.dbo.STCKHEAD STCKHEAD
WHERE DESIHIST.SEASON = STCKHEAD.SEASON AND DESIHIST.DESIGN =
STCKHEAD.DESIGN AND STCKHEAD.SEASON = PRCEHEAD.SEASON AND
STCKHEAD.PRODUCT
=
PRCEHEAD.PRODUCT AND STCKHEAD.PRICE = PRCEHEAD.PRICE AND
((DESIHIST.ACCOUNT='002') AND (DESIHIST.CARDSHIP>0) AND
(DESIHIST.MONTH='200604') OR (DESIHIST.ACCOUNT='003') AND
(DESIHIST.CARDSHIP>0) AND (DESIHIST.MONTH='200604'))

What I need to do is aggregate this up to account level so rather than
for
example seeing 2000 records returned which shows shipments and costs at
the
lowest level what I need to do is summarise this by account which will
give
me 2 records but which adds up all the shipments by account.

For example store 1 cardsshipped (sum of indivcards); store 2
cardshipped;
store 3 cardshipped etc.

Hope this can be followed without seeing it in a spreadsheet.

Thanks

Reggiee
Click to expand...
Click to expand...
Click to expand...
 
Hi again Steve,

Really struggling with this now and the grouping.

SQL one which gives me all the data but not grouped by account is as follows

SELECT DESIHIST.ACCOUNT, DESIHIST.DESIGN, DESIHIST.CARDSHIP, DESIHIST.MONTH,
DESIHIST.SEASON, PRCEHEAD.WHOLESAL AS 'PACKCOST', PRCEHEAD.RETAIL,
PRCEHEAD.UNITCONT, STCKHEAD.PRICE, STCKHEAD.PRODUCT,
(DESIHIST.CARDSHIP/PRCEHEAD.UNITCONT) AS 'PACKS',
((DESIHIST.CARDSHIP/PRCEHEAD.UNITCONT)*PRCEHEAD.WHOLESAL) AS 'TOTALCOST',
(DESIHIST.CARDSHIP*PRCEHEAD.RETAIL) AS 'RETAILCOST'
FROM CMCompanyA.dbo.DESIHIST DESIHIST, CMCompanyA.dbo.PRCEHEAD PRCEHEAD,
CMCompanyA.dbo.STCKHEAD STCKHEAD
WHERE DESIHIST.SEASON = STCKHEAD.SEASON AND DESIHIST.DESIGN =
STCKHEAD.DESIGN AND STCKHEAD.SEASON = PRCEHEAD.SEASON AND STCKHEAD.PRODUCT =
PRCEHEAD.PRODUCT AND STCKHEAD.PRICE = PRCEHEAD.PRICE AND
((DESIHIST.CARDSHIP>0) AND (DESIHIST.MONTH='200604'))
ORDER BY DESIHIST.ACCOUNT

I want to sum the RETAILCOST column and summarise by account

So ive now got this SQL which does no grouping at all

SELECT DESIHIST.ACCOUNT, DESIHIST.DESIGN, DESIHIST.CARDSHIP, DESIHIST.MONTH,
DESIHIST.SEASON, PRCEHEAD.WHOLESAL AS 'PACKCOST', PRCEHEAD.RETAIL,
PRCEHEAD.UNITCONT, STCKHEAD.PRICE, STCKHEAD.PRODUCT,
(DESIHIST.CARDSHIP/PRCEHEAD.UNITCONT) AS 'PACKS',
((DESIHIST.CARDSHIP/PRCEHEAD.UNITCONT)*PRCEHEAD.WHOLESAL) AS 'TOTALCOST',
SUM(DESIHIST.CARDSHIP*PRCEHEAD.RETAIL) AS 'RETAILCOST'
FROM CMCompanyA.dbo.DESIHIST DESIHIST, CMCompanyA.dbo.PRCEHEAD PRCEHEAD,
CMCompanyA.dbo.STCKHEAD STCKHEAD
WHERE DESIHIST.SEASON = STCKHEAD.SEASON AND DESIHIST.DESIGN =
STCKHEAD.DESIGN AND STCKHEAD.SEASON = PRCEHEAD.SEASON AND STCKHEAD.PRODUCT =
PRCEHEAD.PRODUCT AND STCKHEAD.PRICE = PRCEHEAD.PRICE AND
((DESIHIST.CARDSHIP>0) AND (DESIHIST.MONTH='200604'))
GROUP BY DESIHIST.ACCOUNT, DESIHIST.DESIGN, DESIHIST.CARDSHIP,
DESIHIST.MONTH, DESIHIST.SEASON, PRCEHEAD.WHOLESAL, PRCEHEAD.RETAIL,
PRCEHEAD.UNITCONT, STCKHEAD.PRICE, STCKHEAD.PRODUCT
ORDER BY DESIHIST.ACCOUNT

Any clues ??

Thanks in advance

Reggiee

[MVP] S.Clark said:
As long as you understand to replace the word whatever with whatever it is
that you need to sum.

Reggiee said:
Thanks Steve

Did try that and it didnt like it - will try and copy over the sql incl
the
group by bit and see if theres something silly in there

Reggiee

[MVP] S.Clark said:
Use the GROUP BY clause to summarise

Select ACCOUNT, SUM([whatever]) from Tablename GROUP BY ACCOUNT


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hi All

Below is the query Ive been running which gives me a summary of all
shipments (indivcards) to individual stores (accounts) for one chosen
month.

SELECT DESIHIST.ACCOUNT, DESIHIST.CARDSHIP, DESIHIST.MONTH,
DESIHIST.SEASON,
PRCEHEAD.WHOLESAL AS 'PACKCOST', PRCEHEAD.RETAIL, PRCEHEAD.UNITCONT,
STCKHEAD.PRICE, STCKHEAD.PRODUCT, (DESIHIST.CARDSHIP*PRCEHEAD.UNITCONT)
AS
'INDIVCARDS', ROUND((DESIHIST.CARDSHIP*PRCEHEAD.WHOLESAL),2) AS
'TOTALCOST'
FROM CMCompanyA.dbo.DESIHIST DESIHIST, CMCompanyA.dbo.PRCEHEAD
PRCEHEAD,
CMCompanyA.dbo.STCKHEAD STCKHEAD
WHERE DESIHIST.SEASON = STCKHEAD.SEASON AND DESIHIST.DESIGN =
STCKHEAD.DESIGN AND STCKHEAD.SEASON = PRCEHEAD.SEASON AND
STCKHEAD.PRODUCT
=
PRCEHEAD.PRODUCT AND STCKHEAD.PRICE = PRCEHEAD.PRICE AND
((DESIHIST.ACCOUNT='002') AND (DESIHIST.CARDSHIP>0) AND
(DESIHIST.MONTH='200604') OR (DESIHIST.ACCOUNT='003') AND
(DESIHIST.CARDSHIP>0) AND (DESIHIST.MONTH='200604'))

What I need to do is aggregate this up to account level so rather than
for
example seeing 2000 records returned which shows shipments and costs at
the
lowest level what I need to do is summarise this by account which will
give
me 2 records but which adds up all the shipments by account.

For example store 1 cardsshipped (sum of indivcards); store 2
cardshipped;
store 3 cardshipped etc.

Hope this can be followed without seeing it in a spreadsheet.

Thanks

Reggiee
Click to expand...
Click to expand...
Click to expand...
 
Back
Top