0

I'm trying to alter a current mysql view I have so that when I export the view I have a unique row ID.

I'm trying to find a way to possibly use the row number or an auto increment function but I don't know how to do this in a view.

Here is the query:

alter view daily_report as SELECT 
@id := @id + 1 as id
,c.extension as Extension
,RESPONSIBLEUSEREXTENSIONID as ExtID
, sum(Duration) as Total_Talk_Time_seconds
, round(sum(Duration) / 60,2) as Total_Talk_Time_minutes
, sum(if(LEGTYPE1 = 1,1,0)) as Total_Outbound   
, sum(if(LEGTYPE1 = 2,1,0)) as Total_Inbound
, sum(if(Answered = 1,0,1)) as Missed_Calls
, count(DISTINCT b.NOTABLECALLID) as Total_Calls
, NOW()
, curdate()
FROM cdrdb.session a
LEFT JOIN cdrdb.callsummary b
        ON a.NOTABLECALLID = b.NOTABLECALLID
LEFT join cdrdb.mxuser c
      ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
      CROSS JOIN (SELECT @id := 0 ) as var
WHERE b.ts >= curdate()
AND c.extension IN (7295,7306,7218,7247,7330,7000,7358)
group by c.extension;

Is there a way to use row count or something in my select statement so that my view has unique row IDs?

Geoff_S
  • 4,917
  • 7
  • 43
  • 133

1 Answers1

1

Use user variables

 SELECT @id := @id + 1 as id, <other field>
 FROM ( <your tables> )
 CROSS JOIN (SELECT @id := 0 ) as var
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • So I would put parenthesis around all my current code after from and then do the cross JOIN? – Geoff_S Aug 11 '17 at 16:17
  • Nope. Just add the `CROSS JOIN` at the end of your `FROM` tables and before the `WHERE` – Juan Carlos Oropeza Aug 11 '17 at 16:21
  • Ok, that fixed the syntax errors, I updated with the new query but now I get an error that my SELECT contains variable or parameter? – Geoff_S Aug 11 '17 at 16:23
  • This is a view, so would I have to alter this slightly? – Geoff_S Aug 11 '17 at 16:49
  • 1
    Looks like you cant use variables in a view. Either try this one. https://stackoverflow.com/questions/15891993/create-a-view-with-column-num-rows-mysql or use a sproc instead of a view like this https://stackoverflow.com/questions/13621924/generate-unique-long-id-for-a-view-in-mysql – Juan Carlos Oropeza Aug 11 '17 at 17:05