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
 Aggregate Function Problem.

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-20 : 14:42:35
I have the following SQL statement which works properly: 

SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq
  FROM             jodrtg
                INNER JOIN
                   jomast
                ON jodrtg.fjobno = jomast.fjobno
             INNER JOIN
                joitem
             ON joitem.fjobno = jomast.fjobno
          INNER JOIN
             inmast
          ON jomast.fpartno = inmast.fpartno
         AND jomast.fpartrev = inmast.frev
         AND jomast.fac = inmast.fac
       INNER JOIN
          prinx
       ON inmast.fpartno = prinx.fcpartno
      AND inmast.frev = prinx.fcpartrv
      AND inmast.fac = prinx.fac
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0


I need to return all of these values along with the count of component parts found in the "inboms" table. I have the correct join statement I believe. 

SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq,
       count(inboms.fparentrev) AS Component
  FROM                jodrtg
                   INNER JOIN
                      jomast
                   ON jodrtg.fjobno = jomast.fjobno
                INNER JOIN
                   joitem
                ON joitem.fjobno = jomast.fjobno
             INNER JOIN
                inmast
             ON jomast.fpartno = inmast.fpartno
            AND jomast.fpartrev = inmast.frev
            AND jomast.fac = inmast.fac
          INNER JOIN
             prinx
          ON inmast.fpartno = prinx.fcpartno
         AND inmast.frev = prinx.fcpartrv
         AND inmast.fac = prinx.fac
       INNER JOIN
          inboms
       ON inmast.frev = inboms.fparentrev
      AND inmast.fpartno = inboms.fparent
      AND inmast.fac = inboms.pfacilityid
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0


Next of course I get the following error:
Column jodrtg.fpro_id is invalid in the select list because it is not contained in an aggregate function and there is no group by clause.

When I try to add: 

Group by jodrtg.fpro_id,
       jomast.fpartno,
       jomast.fpartrev,
       inmast.fdescript,
       inmast.fbuyer,
       jodrtg.fopermemo,
       jodrtg.fjobno,
       joitem.fdescmemo,
       inmast.fgroup,
       jodrtg.foperno,
       jodrtg.factschdst,
       jomast.fstatus,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty)),
       jodrtg.foperqty,
       jodrtg.fnqty_comp,
       jodrtg.fnqty_togo,
       jomast.fquantity,
       jomast.factschdfn,
       prinx.fnmaxord


I get the following error:

SQL Server Database Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Help?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-11-20 : 14:47:26
jsut quick and dirty so I'm not 100% sure that it'll work, but try this: 
   SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq,
		(
			SELECT count(inboms.fparentrev) 
			FROM inboms
			WHERE inmast.frev = inboms.fparentrev
			  AND inmast.fpartno = inboms.fparent
			  AND inmast.fac = inboms.pfacilityid
		)AS Component
  FROM                jodrtg
                   INNER JOIN
                      jomast
                   ON jodrtg.fjobno = jomast.fjobno
                INNER JOIN
                   joitem
                ON joitem.fjobno = jomast.fjobno
             INNER JOIN
                inmast
             ON jomast.fpartno = inmast.fpartno
            AND jomast.fpartrev = inmast.frev
            AND jomast.fac = inmast.fac
          INNER JOIN
             prinx
          ON inmast.fpartno = prinx.fcpartno
         AND inmast.frev = prinx.fcpartrv
         AND inmast.fac = prinx.fac
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-20 : 14:52:59
I appreciate that, but I knew I could do it as a subquery. However, doesn't that subquery run over and over again for every part and is therefore terribly inefficent?

I'm not arguing, I just want to make sure I am thinking correctly.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-11-20 : 15:14:02
That is true. But, a correlated sub-query is not always the least effecient method. But, often times it is... depends on the data.

You would have to GROUP BY all the columns that are not contained in an agregate function. However, I'm not sure what you want the COUNT to be, so grouping by all teh columns might not give you the answer you want. Another option is to use a dirived table:
SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq,
       T.fparentrevcnt AS Component
  FROM                jodrtg
                   INNER JOIN
                      jomast
                   ON jodrtg.fjobno = jomast.fjobno
                INNER JOIN
                   joitem
                ON joitem.fjobno = jomast.fjobno
             INNER JOIN
                inmast
             ON jomast.fpartno = inmast.fpartno
            AND jomast.fpartrev = inmast.frev
            AND jomast.fac = inmast.fac
          INNER JOIN
             prinx
          ON inmast.fpartno = prinx.fcpartno
         AND inmast.frev = prinx.fcpartrv
         AND inmast.fac = prinx.fac
       INNER JOIN
         (
			SELECT 
				fparentrev,
				fparent,
				pfacilityid,
				count(inboms.fparentrev) AS fparentrevcnt
			FROM 
				inbons
			GROUP BY
				fparentrev,
				fparent,
				pfacilityid				
         ) AS T
       ON inmast.frev = T.fparentrev
      AND inmast.fpartno = T.fparent
      AND inmast.fac = T.pfacilityid
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-20 : 16:34:20
Thanks Lamprey, that'll do it.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-20 : 16:43:58
How about something like this?

If the count of inboms.fcomponent is greater than 1, I want it to say 'multi' and if it's only 1 it should list the componenent. Here is what I have so far.. 

SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN inboms.fcomponent
           ELSE 'Multi'
        END)
          AS Component
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid


Error is: SQL Server Database Error: Column 'inboms.fcomponent' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-11-21 : 03:04:41
Try


SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
           ELSE 'Multi'
        END)
          AS Component
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-21 : 03:08:20
if inboms.fcomponent is nullable this will return multi for even cases when there's no value present.
Also inboms.fcomponent should be casted to varchar if its not else case will throw error.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-21 : 09:16:05
It shouldn't throw have any zeroes but I rewrote it like this to account for that. 

SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
					 When 0 then null
           ELSE 'Multi'
        END)
          AS Component
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-11-21 : 09:40:00
quote:
Originally posted by DavidChel

It shouldn't throw have any zeroes but I rewrote it like this to account for that. 

SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
					 When 0 then null
           ELSE 'Multi'
        END)
          AS Component
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid


So is this workable solution?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-21 : 09:53:36
Well, the subquery works now. However, when I put them together like this: 

SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq,
       T.subcount AS Component
  FROM                jodrtg
                   INNER JOIN
                      jomast
                   ON jodrtg.fjobno = jomast.fjobno
                INNER JOIN
                   joitem
                ON joitem.fjobno = jomast.fjobno
             INNER JOIN
                inmast
             ON jomast.fpartno = inmast.fpartno
            AND jomast.fpartrev = inmast.frev
            AND jomast.fac = inmast.fac
          INNER JOIN
             prinx
          ON inmast.fpartno = prinx.fcpartno
         AND inmast.frev = prinx.fcpartrv
         AND inmast.fac = prinx.fac
       INNER JOIN
         (
SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
					 When 0 then null
           ELSE 'Multi'
        END)
          AS subcount
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid		
         ) AS T
       ON inmast.frev = T.fparentrev
      AND inmast.fpartno = T.fparent
      AND inmast.fac = T.pfacilityid
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0



And I get the following error:

SQL Server Database Error: Invalid column name 'fparentrev'.
Invalid column name 'fparent'.
Invalid column name 'pfacilityid'.

So, I added the table names to the group by: 


SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq,
       T.subcount AS Component
  FROM                jodrtg
                   INNER JOIN
                      jomast
                   ON jodrtg.fjobno = jomast.fjobno
                INNER JOIN
                   joitem
                ON joitem.fjobno = jomast.fjobno
             INNER JOIN
                inmast
             ON jomast.fpartno = inmast.fpartno
            AND jomast.fpartrev = inmast.frev
            AND jomast.fac = inmast.fac
          INNER JOIN
             prinx
          ON inmast.fpartno = prinx.fcpartno
         AND inmast.frev = prinx.fcpartrv
         AND inmast.fac = prinx.fac
       INNER JOIN
         (
SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
					 When 0 then null
           ELSE 'Multi'
        END)
          AS subcount
  FROM inboms
GROUP BY inboms.fparentrev, inboms.fparent, inboms.pfacilityid		
         ) AS T
       ON inmast.frev = T.fparentrev
      AND inmast.fpartno = T.fparent
      AND inmast.fac = T.pfacilityid
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0



I still get the following error:
SQL Server Database Error: Invalid column name 'fparentrev'.
Invalid column name 'fparent'.
Invalid column name 'pfacilityid'.
Invalid column name 'fparentrevcnt'.

I hate to keep asking, but what the heck am I doing wrong?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-21 : 10:06:16
do you have those columns in inboms?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-21 : 10:11:19
Yes, this code for the subquery only works: 

SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
					 When 0 then null
           ELSE 'Multi'
        END)
          AS Component
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid


Does it matter that in the following that I am not actually calling fparentrev, fparent, pfacilityid in the select statement? 

SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq,
       T.subcount AS Component
  FROM                jodrtg
                   INNER JOIN
                      jomast
                   ON jodrtg.fjobno = jomast.fjobno
                INNER JOIN
                   joitem
                ON joitem.fjobno = jomast.fjobno
             INNER JOIN
                inmast
             ON jomast.fpartno = inmast.fpartno
            AND jomast.fpartrev = inmast.frev
            AND jomast.fac = inmast.fac
          INNER JOIN
             prinx
          ON inmast.fpartno = prinx.fcpartno
         AND inmast.frev = prinx.fcpartrv
         AND inmast.fac = prinx.fac
       INNER JOIN
         (
SELECT (CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
					 When 0 then null
           ELSE 'Multi'
        END)
          AS subcount
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid		
         ) AS T
       ON inmast.frev = T.fparentrev
      AND inmast.fpartno = T.fparent
      AND inmast.fac = T.pfacilityid
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-21 : 10:15:44
Yup. it will matter. without selecting them you wont get it outside for joining. so it should be


SELECT fparentrev, fparent, pfacilityid,(CASE count(inboms.fcomponent)
           WHEN 1 THEN MIN(inboms.fcomponent)
					 When 0 then null
           ELSE 'Multi'
        END)
          AS subcount
  FROM inboms
GROUP BY fparentrev, fparent, pfacilityid
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-11-21 : 10:17:43
What the heck would I do without you guys? Thanks much, this works. 

SELECT jodrtg.fpro_id AS work_center,
       jomast.fpartno AS partno,
       jomast.fpartrev AS partrev,
       inmast.fdescript AS item_desc,
       inmast.fbuyer AS planner,
       jodrtg.fopermemo AS op_desc,
       jodrtg.fjobno AS jobno,
       joitem.fdescmemo,
       inmast.fgroup AS group_code,
       jodrtg.foperno AS op_no,
       jodrtg.factschdst AS op_start,
       jomast.fstatus AS job_status,
       (jodrtg.fsetuptime + (jodrtg.fuprodtime * jodrtg.foperqty))
          AS est_op_time,
       jodrtg.foperqty AS op_quantity,
       jodrtg.fnqty_comp AS op_qty_complete,
       jodrtg.fnqty_togo AS op_qty_to_go,
       jomast.fquantity AS total_job_quant,
       jomast.factschdfn AS complete_date,
       prinx.fnmaxord AS moq,
       T.subcount AS Component
  FROM                jodrtg
                   INNER JOIN
                      jomast
                   ON jodrtg.fjobno = jomast.fjobno
                INNER JOIN
                   joitem
                ON joitem.fjobno = jomast.fjobno
             INNER JOIN
                inmast
             ON jomast.fpartno = inmast.fpartno
            AND jomast.fpartrev = inmast.frev
            AND jomast.fac = inmast.fac
          INNER JOIN
             prinx
          ON inmast.fpartno = prinx.fcpartno
         AND inmast.frev = prinx.fcpartrv
         AND inmast.fac = prinx.fac
       INNER JOIN
          (SELECT fparentrev,
                  fparent,
                  pfacilityid,
                  (CASE count(inboms.fcomponent)
                      WHEN 1 THEN MIN(inboms.fcomponent)
                      WHEN 0 THEN NULL
                      ELSE 'Multi'
                   END)
                     AS subcount
             FROM inboms
           GROUP BY fparentrev, fparent, pfacilityid) AS T
       ON inmast.frev = T.fparentrev
      AND inmast.fpartno = T.fparent
      AND inmast.fac = T.pfacilityid
 WHERE jomast.fstatus IN ('released', 'started', 'open')
   AND jodrtg.fnqty_comp <> jodrtg.fnqty_togo
   AND jodrtg.fnqty_togo <> 0
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-21 : 10:19:52
You're welcome
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site