SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Count Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gerald30
Starting Member

Philippines
46 Posts
Posted - 05/11/2012 :  02:53:18  Show Profile  Reply with Quote
Hello All

Please Help me again this one.

I`am trying to query the details of a Model per process.

Basically each Big book has one model and 18 small books.

I want to show the data like the one below.

---------|---------|-----------------|-------|----------------|------------------|
Sequence | Process | Production Date | Model | Big book Count | Small Book Count |
---------|---------|-----------------|-------|----------------|------------------|
1        | Cutting |    2012-01-30   |  GSX1 |     3          |        54        |


But using my query it gives me this result.

---------|---------|-----------------|-------|----------------|------------------|
Sequence | Process | Production Date | Model | Big book Count | Small Book Count |
---------|---------|-----------------|-------|----------------|------------------|
1        | Cutting |    2012-01-30   |  GSX1 |     3          |        12        |



Can you please tell me what is wrong with my query?

Please Check.

select
x.Sequence as 'Sequence',
x.Process as 'Process',
x.pdt as 'Production Date',
x.mdl as 'Model',
count(x.bigbook) as 'Bigbook Count',
max(x.sb) as 'Smallbook'
from
(
select distinct
'1' as 'Sequence',
'Cutting' as 'Process',
cut_pdt as 'PDT',
cut_shf as 'Shift',
cut_mdl as 'Mdl',
cut_bbn as 'Bigbook',
 count(cut_sbn) as 'SB',
 sum(cut_inp_qty) as 'Input',
 sum(cut_out_qty) as 'Output',
 sum(cut_ng_qty) as 'NG'
from sum_cut_001 group by cut_bbn) x
group by x.mdl



Thank you in advance.

khtan
In (Som, Ni, Yak)

Singapore
16769 Posts
Posted - 05/11/2012 :  03:56:50  Show Profile  Reply with Quote
please post some sample data and the corresponding expected result

KH
Time is always against us
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
311 Posts
Posted - 05/11/2012 :  05:47:31  Show Profile  Reply with Quote 
Looking at your code you are retrieving max from smallbook that might be 12 which is being displayed.If you want count of smallbook 
Please use count function.If you still dont get result please provide 
sample data.

select
x.Sequence as 'Sequence',
x.Process as 'Process',
x.pdt as 'Production Date',
x.mdl as 'Model',
count(x.bigbook) as 'Bigbook Count',
--max(x.sb) as 'Smallbook'
count(x.sb) as 'Smallbook'
from
(
select distinct
'1' as 'Sequence',
'Cutting' as 'Process',
cut_pdt as 'PDT',
cut_shf as 'Shift',
cut_mdl as 'Mdl',
cut_bbn as 'Bigbook',
 count(cut_sbn) as 'SB',
 sum(cut_inp_qty) as 'Input',
 sum(cut_out_qty) as 'Output',
 sum(cut_ng_qty) as 'NG'
from sum_cut_001 group by cut_bbn) x
group by x.mdl,x.Sequence,x.Process,x.pdt

Also for grouping you should specify all columns in group by clause which are being retrieved in select query.And also make sure that for all columns data should be same.
Edited by - vijays3 on 05/11/2012 05:54:00
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48064 Posts
Posted - 05/11/2012 :  15:20:36  Show Profile  Reply with Quote
i dont think so posted query is working query as you're having lots of fields in select which you've not even included in group by.
Also best thing would be to post sample data as Tan suggested rather than posting query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000