group by sum and total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

group by sum and total

Author

Topic

mohdmartin
Starting Member

22 Posts

Posted - 2007-10-22 : 07:39:50

we have following table sturcture

CREATE TABLE [PPE_STOCK] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_RECEIVED] [datetime] NULL ,
[QUANTITY_PPE] [int] NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [PPE_ITEM_ISSUE] (
[PII_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QUANTITY_ISSUE] [int] NULL ,
[REMARKS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [EMPLOYEE] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FIRST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

data insert like this

insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH01','SAFETY HELMET',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH02','SAFETY GLOVES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH03','SAFETY SHOES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH04','SAFETY GLASSES',100)
.....

insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',2)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006365','sh01',1)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006366','sh02',5)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',5)
.....

insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006367','Martin','1')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006365','Peter','2')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006366','Jones','3')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006368','Peter','4')
.....

How can retrived the data by the query like this group by sum
and total.

PPE_STOCK : SAFETY HELMET total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....1...
p0006365..Peter......2
p0006366..Jones......1
-------------------------------------------------------
..............................4......4%

PPE_STOCK : SAFETY GLOVES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....5...
p0006365..Peter......2
p0006366..Jones......5
-------------------------------------------------------
..............................12......12%

PPE_STOCK : SAFETY SHOES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....4...
p0006365..Peter......4
p0006366..Jones......6
-------------------------------------------------------
..............................14......14%
...
...

Regards
Martin

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-22 : 14:56:24

You want to write a report, not a SELECT query. Use a reporting tool such as Reporting Services, Access or Crystal to generate your output with group headers and footer and indents and subtotals.

- Jeff
http://weblogs.sqlteam.com/JeffS

mohdmartin
Starting Member

22 Posts

Posted - 2007-10-23 : 04:05:40

Thanks for your response.
I want to retrieve records by select query.
select query can be retrieve records like above , how ?

Martin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 04:22:11

Select query can retrieve records but to present it like above, you should use front end tool or reporting tool as suggested.

The select query will be something like this


select i.PPE_ID, i.EMP_ID, 
       TOTAL_QUANTITITY_PPE = sum(QUANTITITY_PPE)
from   ppe_item_issue i 
       inner join EMPLOYEE e  on i.EMP_ID = e.EMP_ID
       inner join ppe_stock s on i.PPE_ID = s.PPE_ID
group by i.PPE_ID, i.EMP_ID

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 05:18:33

quote:
Originally posted by mohdmartin

Thanks for your response.
I want to retrieve records by select query.
select query can be retrieve records like above , how ?

Martin

Where do you want to show the data?

Madhivanan

Failing to plan is Planning to fail

mohdmartin
Starting Member

22 Posts

Posted - 2007-10-23 : 06:54:29

Thanks you very much, it is very helpful for me.
I am trying to retrieve live records for management by asp programmed on intranet.
Crystal report use for secretary.

select i.PPE_ID,s.ppe_name,i.EMP_ID,e.name, i.quantity_issue,i.date_issue,
QUANTITY_PPE = sum(QUANTITY_PPE)
from ppe_item_issue i
inner join EMPLOYEE e on i.EMP_ID = e.EMP_ID
inner join ppe_stock s on i.PPE_ID = s.PPE_ID
group by i.PPE_ID, i.EMP_ID, e.name, s.ppe_name,i.quantity_issue,i.date_issue
order by s.ppe_name,e.name

how can use GROUP BY SUM S.PPE_NAME in your query ?
we have to show Safety Helmet records separate.
each group show separate sum.
i.e. total 100 Safety Helmet 25 issue to different employee

regards
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources