Hi all, can work this out doing it a long way by making individual
tables and then inner joining them all, but the code gets real messy.
I'm not really sure how to use case statements but I'm sure they would
be easily applied in this situation.

Ok the problem:

Select 1
select pro_job Shipment, cast(sum(cast(finalvalue as float(10)))as
money) WMT from chd_job_sample_scheme_analyte
where pro_job like 'SHIP%'
and analytecode = 'PROVWMT'
group by pro_job

select 2
select  pro_job Shipment, cast(avg(cast(finalvalue as float(4)))as
money) MOIST from chd_job_sample_scheme_analyte
where pro_job like 'SHIP%'
and analytecode = 'PROVMOIST'
group by Pro_job

select 3

select  pro_job Shipment, cast(sum(cast(finalvalue as float(10)))as
money) DMT from chd_job_sample_scheme_analyte
where pro_job like 'SHIP%'
and analytecode = 'PROVDMT'
group by pro_job

what I want is to join these together so that I get

Shipment | WMT | MOIST | DMT

In addition there will be other coulumns to be added to the table, but
with a gentle push in the general direction will give me some
inspiration.

Any help would be great

Trying something similar to this, but the first select query returns
about 17 rows.

create table #provtable1 (Shipment varchar, WMT  float(20), MOIST float
(20))

Insert into #provtable1 select

(select distinct pro_job  from chd_job_sample_scheme_analyte
where pro_job like 'SHIP%'
and analytecode = 'PROVWMT') Shipment,

(select   cast(sum(cast(finalvalue as float(20)))as money) from
chd_job_sample_scheme_analyte
where pro_job like 'SHIP%'
and analytecode = 'PROVWMT'
group by pro_job) WMT,

(select cast(avg(cast(finalvalue as float(20)))as money) from
chd_job_sample_scheme_analyte
where pro_job like 'SHIP%'
and analytecode = 'PROVMOIST'
group by pro_job) MOIST

select * from #provtable1

drop table #provtable1

and I get this error

Server: Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

Stopher

I'd go with the first INSERT  , now that you  have a primary key , run
UPDATE  instead of insert

(Untested) , I hope I gave an idea


create table #provtable1 (Shipment varchar, WMT  float(20), MOIST float
(20))

Insert into #provtable1 select

(select distinct pro_job  from chd_job_sample_scheme_analyte
where pro_job like 'SHIP%'
and analytecode = 'PROVWMT') Shipment,

----------
UPDATE #provtable1  SET WMT=(select   cast(sum(cast(finalvalue as
float(20)))as money) from
chd_job_sample_scheme_analyte where pro_job =  #provtable1. pro_job   and
analytecode = 'PROVWMT' )
WHERE EXISTS ( SELECT * from
chd_job_sample_scheme_analyte where pro_job =  #provtable1. pro_job )

UPDATE #provtable1  SET MOIST =(select cast(avg(cast(finalvalue as
float(20)))as money) from
chd_job_sample_scheme_analyte where pro_job =  #provtable1. pro_job    AND
analytecode = 'PROVMOIST')
WHERE EXISTS ( SELECT * from
chd_job_sample_scheme_analyte where pro_job =  #provtable1. pro_job )





Show quoteHide quote

Thanks for your responce. I was trying UPDATE but couldn't figure out
the WHERE statement. Will give this a go tomorrow and thanks again for
your responce.

On 29 Aug 2006 21:43:12 -0700, Stopher wrote:

Hi Stopher,

Try if this works:

SELECT   pro_job AS Shipment,
         CAST(SUM(CAST(CASE WHEN analytecode = 'PROVWMT'
                            THEN finalvalue
                       END AS float(10))) AS money) AS WMT,
         CAST(AVG(CAST(CASE WHEN analytecode = 'PROVMOIST'
                            THEN finalvalue
                       END AS float(4))) AS money) AS MOIST,
         CAST(SUM(CAST(CASE WHEN analytecode = 'PROVDMT'
                            THEN finalvalue
                       END AS float(10))) AS money) AS DMT
FROM     chd_job_sample_scheme_analyte
WHERE    pro_job LIKE 'SHIP%'
AND      analytecode IN ('PROVWMT', 'PROVMOIST', 'PROVDMT')
GROUP BY pro_job;

BTW, do you really need all this casting to and fro?

Unfortunately yes, the data is stored as varchar, and for it to come
out as a value it need converting to SUM, I guess some of the casting
can be trimmed and converts added to get the right format, or I could
format the values when they output to the report/spreadsheet.

This worked a treat by the way. I also have another problem with
sumproduct calculation in another thread if you could take a look at
that, same application I'm trying to achieve.

Regards

Stopher

On 31 Aug 2006 14:40:59 -0700, Stopher wrote:

Hi Stopher,

Best is to convert the data to numeric when storing it in the DB.
Storing it as varchar means that someday you WILL have non numeric data
in your table, and then your query will error out.

But even as it is, yoou can reduce the number of conversions - no need
to convert to float first, then add, then convert to money. Go straight
to money if that's what yoou need (though I personally dislike the
datatype, for numeroous reasons). Yoou shoould never use money in
multiplication or division, but it's okay for addition.

So instead of

         CAST(SUM(CAST(CASE WHEN analytecode = 'PROVWMT'
                            THEN finalvalue
                       END AS float(10))) AS money) AS WMT,

you would get

         SUM(CAST(CASE WHEN analytecode = 'PROVWMT'
                       THEN finalvalue
                  END AS money)) AS WMT,


Wait a minute, I'll search for it.


Okay, I I've found it. If you want a solution, then I suggest that you
post, _IN THAT THREAD (not here!!)_, the following information:

* Structure of your tables, in the form of CREATE TABLE statements. You
may omit irrelevant columns but please do include all constraints,
properties, indexes, etc.

* Some well-chosen rows of sample data to illustrate the problem. Post
them as INSERT statements.

* Expected results based on provided sample data.

* Explanation of how the results are derived from the sample data (this
is already present in that thread; that's why I ask you to post there).

Test your CREATE TABLE and INSERT statements before posting, by creating
an empty database, and executing the statements from Query Analyzer or
SQL Server Management Studio in that empty database.

help with query
How to erase hundreds of DEFAULT values?
Indexing question
Disable all constraints
Setting the Seed of an IDENTITY field on a temp table programatica