CODEWITHSUNDEEP
sqloracle-database
Nandhini Devi
Nandhini Devi

Reputation: 99

Error while counting Column (Count1) in oracle

ORA-00904: "COUNT1": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

Error at Line: 8 Column: 73

SELECT a1.branch_name        AS myfavourite,
       COUNT(a1.branch_name) AS count1,
       SUM(count1)           AS total_purchase ,
       a1.branch_id          AS branch_id,
       c1.city,
       c1.Branch_description AS description,
       c1.userid             AS shopmailid,
       c1.image
FROM tbl_orderdetails a1
INNER JOIN tbl_ordermaster b1
     ON   a1.order_master_id=b1.ordermasterid
INNER JOIN tbl_user c1
     ON   c1.id      =a1.branch_id
WHERE b1.user_id='12'
GROUP BY a1.branch_name,
     a1.branch_id,
     c1.city,
     c1.Branch_description,
     c1.userid,
     c1.image
ORDER BY COUNT(a1.branch_name) DESC

I want to sum count1 and get value in total purchase.I got error like the above

Upvotes: 0

Views: 423

Answers (3)

vinoth_S
vinoth_S

Reputation: 27

Try this,

With Cte(myfavourite,count1,branch_id,city,description,shopmailid,image)
As
(
SELECT a1.branch_name        AS myfavourite,
       COUNT(a1.branch_name) AS count1,
       --SUM(count1)           AS total_purchase ,
       a1.branch_id          AS branch_id,
       c1.city,
       c1.Branch_description AS description,
       c1.userid             AS shopmailid,
       c1.image
FROM tbl_orderdetails a1
INNER JOIN tbl_ordermaster b1   ON   a1.order_master_id=b1.ordermasterid
INNER JOIN tbl_user c1          ON   c1.id      =a1.branch_id
WHERE b1.user_id='12'
GROUP BY a1.branch_name,a1.branch_id,c1.city,c1.Branch_description,
     c1.userid,c1.image
ORDER BY COUNT(a1.branch_name) DESC
)
select sum(Count1) as total_purchase, myfavourite,branch_id,city,description,shopmailid,image from  Cte
group by  myfavourite,branch_id,city,description,shopmailid,image

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191380

The documentation is fairly clear that you can't use a column alias as you are trying to, within the same select list as you can only use it in the order by:

c_alias Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

You can work around that with an in-line view (as Vignesh Kumar showed) or a common table expression (CTE, also called subquery factoring, as vinoth_S showed). But both those answers only show you the total_purchase value, not the individual count1 values for each row in the result set.

You can't just remove the alias and do SUM(COUNT(a1.branch_name)) AS ... since that would give you an ORA-00937 error - the grouping for the sum isn't clear.

You can use the analytic version of SUM to calculate both in one go though:

SELECT a1.branch_name        AS myfavourite,
       COUNT(a1.branch_name) AS count1,
       SUM(COUNT(a1.branch_name)) OVER (PARTITION BY NULL) AS total_purchase ,
...

If the original query got:

MYFAVOURITE     COUNT1  BRANCH_ID CITY       DESCRIPTION SHOPMAILID IMAGE    
----------- ---------- ---------- ---------- ----------- ---------- ----------
BR2                 12         12 Paris      Branch 2            12 image 2    
BR1                 10         11 London     Branch 1            12 image 1    
BR3                  1         13 New York   Branch 4            12 image 3

... then adding that analytic sum would give:

MYFAVOURITE     COUNT1 TOTAL_PURCHASE  BRANCH_ID CITY       DESCRIPTION SHOPMAILID IMAGE    
----------- ---------- -------------- ---------- ---------- ----------- ---------- ----------
BR2                 12             23         12 Paris      Branch 2            12 image 2    
BR1                 10             23         11 London     Branch 1            12 image 1    
BR3                  1             23         13 New York   Branch 4            12 image 3

... with the same overall total count value in all rows in the result set, which you said in comments is what you want.

SQL Fiddle.

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

You can't use any aggregate function for alias columns. The error is because of 

   COUNT(a1.branch_name) AS count1,
   SUM(count1) AS total_purchase

this line..

Try this

Select myfavourite,Sum(count1),branch_id,city,description,shopmailid,image 
From (
      Select a1.branch_name As myfavourite,count(a1.branch_name) As count1,
             a1.branch_id As branch_id, c1.city, c1.Branch_description As description,
             c1.userid As shopmailid,c1.image 
      From tbl_orderdetails a1 
           Inner Join tbl_ordermaster b1 ON a1.order_master_id=b1.ordermasterid 
           Inner Join tbl_user c1 ON c1.id=a1.branch_id  
      Where b1.user_id='12' 
      Group By a1.branch_name,a1.branch_id,c1.city, c1.Branch_description, c1.userid, c1.image
     ) Tmp
Group By myfavourite,branch_id,city,description,shopmailid,image 
Order By myfavourite

Upvotes: -1

Related Questions