Select Count Help - 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

Select Count Help

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-05-11 : 02:53:18

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)

17689 Posts

Posted - 2012-05-11 : 03:56:50

please post some sample data and the corresponding expected result

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

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-11 : 05:47:31

[code]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.
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:20:36

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources