Combine SUM and CAST - not working?

Question

PostgreSQL Unicode 9.01 doesn't like:

SELECT table1.fielda,
SUM (CAST (table2.fielda AS INT)) AS header.specific
FROM *etc*

What is wrong with SUM-CAST?

Error Message:

Incorrect column expression: 'SUM (CAST (specifics_nfl_3pl_work_order_item.delivery_quantity AS INT))

Query:

SELECT specifics_nfl_3pl_work_order.work_order_number,
specifics_nfl_3pl_work_order.goods_issue_date,
specifics_nfl_3pl_work_order.order_status_id,
SUM (CAST (specifics_nfl_3pl_work_order_item.delivery_quantity AS INT)) AS units
FROM public.specifics_nfl_3pl_work_order specifics_nfl_3pl_work_order,
public.specifics_nfl_3pl_work_order_item specifics_nfl_3pl_work_order_item,
public.specifics_nfl_order_status specifics_nfl_order_status
WHERE specifics_nfl_3pl_work_order.order_status_id In (3,17,14) 
AND specifics_nfl_3pl_work_order_item.specifics_nfl_work_order_id=
specifics_nfl_3pl_work_order.id 
AND ((specifics_nfl_3pl_work_order.sold_to_id<>'0000000000') 
AND (specifics_nfl_3pl_work_order.goods_issue_date>={d '2013-08-01'}))

It would be really great if you can help.

Missing: table definition, Postgres version, error message, explanation for query. — Erwin Brandstetter, Aug 10 '13 at 18:34
Thanks Erwin: I have posted the actual code now. I appreciate your reply. — user2670949, Aug 11 '13 at 00:26
Ok, I looked-up ORM. It sounds like MS Query might qualify as ORM..? I began the query in MS Query, but then tried to modify it using Postgre Documentation (yes, I'm very new at this) — user2670949, Aug 11 '13 at 00:35
`PostgreSQL Unicode 9.01` is the *ODBC Driver* version; please `select version()` to get the actual PostgreSQL server version. That said, if it's also 9.0.1, **upgrade now**: That version has a known security vulnerability and you're also missing 12 point-releases of fixes. Seriously, *upgrade to 9.0.13*. — Craig Ringer, Aug 11 '13 at 10:23
You can get the *table definition* by running `\d table1` in [psql](http://stackoverflow.com/tags/psql/info) or you could use a GUI like [pgAdmin](http://stackoverflow.com/tags/pgadmin/info). — Erwin Brandstetter, Aug 11 '13 at 11:37
Also consider fixing your first example. `AS header.specific` is a misleading syntax error. You can't have a dot in an unquoted alias. — Erwin Brandstetter, Aug 11 '13 at 12:21

Roman Pekar · Answer 1 · 2013-08-11T12:08:02.573

If I were you, then I would do these steps:

give your table short aliases
format the query
use proper ANSI joins:
remove spaces between function name and (

select
    o.work_order_number,
    o.goods_issue_date,
    o.order_status_id,
    sum(cast(oi.delivery_quantity as int)) as units
from public.specifics_nfl_3pl_work_order as o
    inner join public.specifics_nfl_3pl_work_order_item as oi on
       oi.specifics_nfl_work_order_id = o.id
    -- inner join public.specifics_nfl_order_status os -- seems redundant
where
    o.order_status_id In (3,17,14) and
    o.sold_to_id <> '0000000000' and
    o.goods_issue_date >= {d '2013-08-01'}

Actually I really think you need group by clause here:

select
    o.work_order_number,
    o.goods_issue_date,
    o.order_status_id,
    sum(cast(oi.delivery_quantity as int)) as units
from public.specifics_nfl_3pl_work_order as o
    inner join public.specifics_nfl_3pl_work_order_item as oi on
       oi.specifics_nfl_work_order_id = o.id
where
    o.order_status_id In (3,17,14) and
    o.sold_to_id <> '0000000000' and
    o.goods_issue_date >= {d '2013-08-01'}
group by
    o.work_order_number,
    o.goods_issue_date,
    o.order_status_id

if it still doesn't work - try to comment sum and see is it working?

+1 on rewriting the query in proper format. Good catch on the missing `GROUP BY`. That's probably the immediate cause of the error. There are more problems ... — Erwin Brandstetter, Aug 11 '13 at 11:47
Thanks @ErwinBrandstetter, readability is one of the top of my priorities, that's why I like SQL and Python so much :) — Roman Pekar, Aug 11 '13 at 12:13

score 0 · Answer 2 · answered Aug 10 '13 at 22:35

0

But you have a table2 or only table1?

Try:
SELECT table1.fielda,
SUM (CAST (table1.fielda AS INT)) AS "header.specific"
FROM etc

answered Aug 10 '13 at 22:35

user2478690

411
2
4

score 0 · Answer 3 · edited May 23 '17 at 11:44

In addition to what @Roman already cleared up, there are more problems here:

SELECT o.work_order_number
      ,o.goods_issue_date
      ,o.order_status_id
      ,SUM(CAST(oi.delivery_quantity AS INT)) AS units -- suspicious
FROM   public.specifics_nfl_3pl_work_order o,
JOIN   public.specifics_nfl_3pl_work_order_item oi
                                     ON oi.specifics_nfl_work_order_id = o.id 
CROSS  JOIN public.specifics_nfl_order_status os -- probably wrong
WHERE  o.order_status_id IN (3,17,14)
AND    o.sold_to_id <> '0000000000'              -- suspicious
AND    o.goods_issue_date> = {d '2013-08-01'}    -- nonsense
GROUP  BY 1, 2, 3

o.goods_issue_date> = {d '2013-08-01'} is syntactical nonsense. Maybe you mean:
o.goods_issue_date> = '2013-08-01'
You have the table specifics_nfl_order_status in your FROM list, but without any expression connecting it to the rest. This effectively results in a CROSS JOIN, which results in a Cartesian product and is almost certainly wrong in a very expensive way: every row is combined with every row of the rest:
CROSS JOIN public.specifics_nfl_order_status os
Either remove the table (since you don't use it) or add a WHERE or ON clause to connect it to the rest. Note, that it is not just redundant, it has a dramatic effect on the result as it is.
This WHERE clause is suspicious:
AND o.sold_to_id <> '0000000000'
Seems like you are storing numbers as strings or otherwise confusing the two. Also, CAST (oi.delivery_quantity AS INT) should not be needed to begin with. The column should be of data type integer or some other appropriate numeric type to begin with. Be sure to use proper data types.
The default setting of search_path includes public, and you may not need to schema-qualify tables. Instead of public.specifics_nfl_3pl_work_order, it may suffice to use:
specifics_nfl_3pl_work_order
GROUP BY 1, 2, 3 is using positional parameters, just a notational shortcut for:
GROUP BY o.work_order_number, o.goods_issue_date, o.order_status_id
Details in the manual.
According to comments you are using MS Query to create the query. This is not the best of ideas. Produces the kind of inferior code you presented us with. You may want to get rid of that while you are working with PostgreSQL.

Wow! Thank you all for "schooling" me. I will look through all of this now that I'm back at the computer. You have been generous in helping-out a newbie. I intend to rely less upon MS Query now and learn how to do it myself. Thanks for not considering this post a waste of your time. I will study it all carefully. — user2670949, Aug 11 '13 at 18:58
btw, all of the code, including `o.goods_issue_date> = {d '2013-08-01'}` nonsense, was generated by MS Query, except the `SUM(CAST)` - because MS Query doesn't do that. But when I added that line the query quit working. — user2670949, Aug 11 '13 at 21:19
... Yes, indeed the database frequently stores numbers as text (and I have no control over that) - that's what got me into the problem I am trying to solve - can't add the text _numbers_. But in the case of `sold_to_id` it is account number - so text is ok. — user2670949, Aug 11 '13 at 21:29
**IT WORKED** (two days later). Now I'm going to try one on my own - no MS Query - for the other element of my report. — user2670949, Aug 11 '13 at 21:57
@user2670949: Congrats! If you should need it, here is some advice [how to change the data type for table columns](http://stackoverflow.com/questions/10343383/rails-migrations-tried-to-change-the-type-of-column-from-string-to-integer/10343444#10343444). — Erwin Brandstetter, Aug 12 '13 at 07:35

Combine SUM and CAST - not working?

3 Answers3